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.