Problem solve Get help with specific problems with your technologies, process and projects.

Processing XML files with SQL Server functions

The procedure of loading XML files onto a SQL Server database and processing them into tables is not well documented. Discover a prescribed method that uses both SQL Server functions and XPath expressions.

Since the release of SQL Server 2000, processing XML data has been a topic of discussion among database administrators....

It is often used when a front-end application passes an XML document to a stored procedure within an input parameter. Sometimes, however, you encounter a folder containing a set of XML files that needs to be loaded into the database and then processed into SQL Server tables. This is more difficult, and documentation on how to do so is sparse.

More on SQL Server features

Learn some of the most useful SQL Server 2012 features

Get ahead with these little-know features of Microsoft SQL Server 2012

Find out what Denny Cherry's 2013 predictions are for SQL Server trends

You can use several techniques, most of which are quite complex. I have used SQL Server Integration Services (SSIS), Data Transformation Services (DTS) and a self-written Windows application to read and load files. My favorite technique, however, is to use the OPENROWSET function. This function enables a good deal of flexibility, because you can control the entire process from each end within your T-SQL stored procedure.

The first step of the process is to create a table with a single column that uses the XML data type. A temporary table also works:

CREATE TABLE #WorkingTable
(Data XML)

With the OPENROWSET function, you then load the data into a single row of the table:

INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'D:\Temp\Sample.xml', SINGLE_BLOB) AS data

You can now place the value from the Data column of the #WorkingTable table into a variable, call on the OPENXML function to parse the XML document and use it as necessary. In this case, the XML document is very basic:

The OPENXML code necessary to read this data from the temporary table should look something like this:

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/root/data', 1)
WITH (Col1 VARCHAR(5) '@parm1',
Col2 VARCHAR(5) '@parm2')

EXEC sp_xml_removedocument @hDoc

The resulting output from this query will look something like this:

This is the basic syntax of all your OPENXML statements. From here, you can add greater complexity to /root/data, @parm1 and the other XPath expressions. This allows you to process data from more complex XML documents. Here is an example of a more complex XML document, with invoice data:

As you can see, we have a three-level document with needed data in the Customer ID, Order ID and Item ID nodes. In this sample code, you can see that we start with the Item ID node and work our way back to the Customer ID and Order ID values:

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, '/root/Customer/Order/Item')
WITH (CustomerId INT '../../@ID',
OrderId INT '../@ID',
ItemId INT '@ID',
Qty INT '@Qty')

EXEC sp_xml_removedocument @hDoc

A record set like this is then produced:

If we were to take this same XML document and add an order for a second customer, we would see an output like this:

As I said, we started at the Item node of the XML document. If we started at the Customer node of the document and worked our way down the document, our output would not be correct. We would instead get only the first item for each Customer ID in the document.

This OPENXML function gives us the following incorrect recordset:

SELECT *
FROM OPENXML(@hDoc, '/root/Customer')
WITH (CustomerId INT '@ID',
OrderId INT 'Order/@ID',
ItemId INT 'Order/Item/@ID',
Qty INT 'Order/Item/@Qty')

In this article, we have discussed basic examples involving the use of XPath. For more comprehensive examples, you can refer to Microsoft's XPath syntax options to leverage the power of the XML engine in T-SQL statements.

ABOUT THE AUTHOR

Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

This was last published in February 2009

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close