Query XML data stored in table text column

• kothiyashreyas

  Newbie

  Points: 7

  More actions

  April 14, 2009 at 4:01 am

  #155554

  I have Source table where there is two column

  DocID int

  Doc Text

  Doc has the following xml data in it.

  i need to query that data and get the value of

  Page_number

  Redaction

  Top

  Left

  Width

  Height

  Text

  i am not familiar with xquery

  and i need to get xml data out from text column

  i think here i should get two rows because redaction comes twise

  if any one can help on that

  Data Stored in Text Column

• Mark Cowne

  One Orange Chip

  Points: 26984

  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