0

I have got xml column in my sql server 2008 database. XML sample in each row of my table

<document>
 <part1>
   <listitem>val1</listitem>
   <listitem>val2</listitem>
   <listitem>val3</listitem>
 </part1>
 <part2>
   <listitem>val4</listitem>
 </part2>
</document>

I would like to select all elements from all rows. From sample above I should get four rows with listitem value.

The answer is

select x.nd.value ('(.)[1]', 'varchar(250)') as ValuesFromXml
from TableWithXmlColumn t cross apply t.XmlContent.nodes (
'//listitem') x(nd);

Thanks for help

marcinn
  • 1,879
  • 2
  • 22
  • 46

1 Answers1

2

You can do it like this:

select Col.value('.', 'varchar(20)') 
from yourtable 
cross apply XmlColumn.nodes('//listitem') as NewTable(Col)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rui Romano
  • 46
  • 4
  • If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! – marc_s Dec 03 '10 at 22:10