Extract elements in xml to rows in select statement

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

Answers


You can do it like this:

select Col.value('.', 'varchar(20)') 
from yourtable 
cross apply XmlColumn.nodes('//listitem') as NewTable(Col)

Need Your Help

search for multiple words in file

linux search grep

If I wish to search for occurrence of a word in a file using grep I use

Should I migrate to ASP.NET MVC?

asp.net-mvc

I just listened to the StackOverflow team's 17th podcast, and they talked so highly of ASP.NET MVC that I decided to check it out.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.