Wednesday, April 30, 2014

SQL Server 2008 - Query XML data

Just recognized, that it is easy to query xml data stored in a SQL Server 2008 table (field):

declare @data xml

select @data = <datafield> from <table> (nolock) where <condition>

select @dataselect @data.query('<node>') as result

Here an example with sample xml:

declare @data xml
select @data = '<root><datas><data><add name="a" value="x1" /><add name="b" value="y2" /> <add name="c" value="z3" /></data><data><add name="a" value="e4" /> <add name="b" value="f5" /><add name="c" value="g6" /> </data></datas></root>' 
select @data
select @data.query('(/root/datas/data/add[@name="c"])[1]') as result 

The result is:

<add name="c" value="z3" /> 

Now I need to find out, how to get the value and not the complete node.

No comments:

Post a Comment