Shredding XML in SQL Server 2005

Using XML data has many applications. In databases in particular it can be used for passing parameters from client applications, exchange data between SQL modules, or storing details in XML columns. SQL Server 2005 offers new capabilities and native support for XML. Below is one example of utilizing some of the new XML methods in XML to shred XML data to table format.

-- Declare XML variable
DECLARE @data XML;
 
-- Element-centered XML
SET @data = 
N'<data>
    <customer>
      <id>1</id>
      <name>Allied Industries</name>
    </customer>
    <customer>
      <id>2</id>
      <name>Trades International</name>
    </customer>
  </data>';
 
-- Using the query() method
SELECT T.customer.query('id').value('.', 'INT') AS customer_id,
       T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
-- Using the value() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
-- Select only customer which id equals 2 using the exist() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer)
WHERE T.customer.exist('id/text()[. = "2"]') = 1;
 
-- Attribute-centered XML
SET @data = 
N'<data>
    <customer id="1" name="Allied Industries"/>
    <customer id="2" name="Trades International"/>
  </data>';
 
-- Using the value() method
SELECT T.customer.value('@id', 'INT') AS customer_id,
       T.customer.value('@name', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
 
-- Results
customer_id customer_name
----------- --------------------
1           Allied Industries
2           Trades International

Testing the processing for element-centered XML versus attribute-centered XML does show that attribute-centered XML is processed faster.

Resources:

XML Support in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345117.aspx