Processing XML files with SQL Server functions

Processing XML files with SQL Server functions

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.

You can use several techniques, most of which are quite complex. I have used SQL Server Integration Services (SSIS),

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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

This was first published in February 2009

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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top