Query XML data stored in table text column

• Mark Cowne

  One Orange Chip

  Points: 26986

  More actions

  April 14, 2009 at 5:18 am

  #977126

  with cte as (

  select DocID,cast(Doc as xml) as Doc

  from mytable)

  select Page.value('@page_number','int') as Page_number,

  Redaction.value('Top[1]','int') as [Top],

  Redaction.value('Left[1]','int') as [Left],

  Redaction.value('Width[1]','int') as Width,

  Redaction.value('Height[1]','int') as Height,

  Redaction.value('Text[1]','varchar(100)') as [Text]

  from cte

  cross apply Doc.nodes('/pages/Page') as x(Page)

  outer apply Page.nodes('redactions/Redaction') as y(Redaction)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537