Search This Blog

Wednesday, October 15, 2014

Reading XML in SQL Server 2005/2008

XML Processing in SQL Server

SQL Server has very good support for reading/processing XML. Here are some of the examples below with different XML structures. I found it extremely handy when using them for passing as collections to stored procedures.

Example 1: Entire data is passed as an attribute so that the XML is short formed. The below example shows how to read the element name along with the attribute value.

SQL :  DECLARE @xml XML = 
'<root>
<offer old="old1" new="new1" module="1" />
<offer old="old2" new="new2" module="2" />
</root>'

SELECT  T.c.value('local-name(.)[1]', 'varchar(100)') AS Element,
        T.c.value('@new', 'varchar(20)') as New
FROM   @xml.nodes('/root/offer') T(c);

Example 2: XML had Node value and as well as attributes
SQL : 
DECLARE @xml XML = 
'
<root>
<item>
    <node1 offer="1">2.09</node1>
    <node2 offer="2">2.97</node2>
</item>
</root>
'

SELECT  T.c.value('local-name(.)[1]', 'varchar(100)') AS Element,
        T.c.value('./text()[1]', 'decimal(17,2)') AS Value,
        T.c.value('@offer', 'varchar(20)') as Offer
FROM   @xml.nodes('//item/child::node()') T(c);

Both the above examples (xml structures), have been very effective and useful ones for me.