I've got XML data, now what?...
I've seen this question or something similar in many places recently in regards to the XML data type that was introduced in SQL Server 2005. I've been using XML with SQL Server for a while, going back to SQL Server 2000, and was very pleased with the additions made in 2005.
First, let's create a table to hold the XML data:
IF OBJECT_ID('dbo.xmlStorage', 'U') IS NOT NULL
DROP TABLE dbo.xmlStorage;
GO
CREATE TABLE dbo.xmlStorage
(
ID INT IDENTITY(1,1)
,XMLDocument XML NOT NULL
);
Simple enough, an ID and an XML column. Now lets add some data.
INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
<Customer CustomerID="1" CustomerName="Charles Schwab">
<Accounts>
<Account AccountID="1" AccountName="Image Consultant" />
<Account AccountID="501" AccountName="Image Consultant South" />
</Accounts>
</Customer>
</Customers>');
INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
<Customer CustomerID="2" CustomerName="Mohawk Industries Inc.">
<Accounts>
<Account AccountID="2" AccountName="Geological Engineer" />
</Accounts>
</Customer>
</Customers>');
INSERT dbo.xmlStorage (XMLDocument) VALUES (
'<Customers>
<Customer CustomerID="3" CustomerName="Wondercat Solutions">
<Accounts>
<Account AccountID="7" AccountName="Ensley Manufacturing" />
</Accounts>
</Customer>
</Customers>');
We have a single Customer node with potential for multiple Account nodes within. We need to get each Customer and their associated Accounts into a record set. To do so we will make use of the .value() and .nodes() options.
The following query will return each Customer node ID and the child AccountID and AccountName:
SELECT
XMLDocument.value('(/Customers/Customer/@CustomerID) [1]', 'INT') AS CustomerID
,Tablename.columnname.value('@AccountID', 'INT') AS AccountID
,Tablename.columnname.value('@AccountName', 'VARCHAR(100)') AS AccountName
FROM
dbo.xmlStorage
CROSS APPLY XMLDocument.nodes('/Customers/Customer/Accounts/Account') AS Tablename(columnname);
XMLDocument.value('(/Customers/Customer/@CustomerID) [1]', 'INT') is used to return the first entry in the Customers node. This is our base if you will.
CROSS APPLY XMLDocument.nodes('/Customers/Customer/Accounts/Account') AS Tablename(columnname) is next and is used to query into the Accounts nodes and return the Account information for each customer. A handle is given for the CROSS APPLY for reference in the SELECT list.
Last we use our handle referencing the columnname.value() for the AccountID and AccountName. In both cases we are referencing the column name and the data type which should be used for output.
And that does it, XML documents read to a result set. Pretty easy stuff, right?
Now you can go out and beat down any hierarchical data structure that comes your way. :)
Enjoy!