Working with XML Documents

I've shown you how to turn relational data into XML data; now let's look at how you can turn XML data into relational data. SQL Server provides features for working with XML documents on the server and even for writing data from XML documents to the database. The OPENXML keyword and two system-stored procedures that I'll discuss provide a way to access XML documents within queries as if they were a standard relational table. However, as you'll discover, the syntax for accessing portions of an XML document is specialized.

OPENXML

Here's a simple example of how OPENXML is typically used. The example simply demonstrates how to query an XML document using a SQL SELECT query.

DECLARE @hdlXmlDoc integer
DECLARE @xmlText varchar(2000)

SET @xmlText = '<?xml  version="1.0" ?>
<RESULTS>
   <Orders OrderID="10643" CustomerID="ALFKI" Freight="29.46" />
   <Orders OrderID="10692" CustomerID="ALFKI" Freight="61.02" />
   <Orders OrderID="10702" CustomerID="ALFKI" Freight="23.94" />
   <Orders OrderID="10835" CustomerID="ALFKI" Freight="69.53" />
   <Orders OrderID="10952" CustomerID="ALFKI" Freight="40.42" />
   <Orders OrderID="11011" CustomerID="ALFKI" Freight="1.21" />
</RESULTS>'

exec sp_xml_preparedocument @hdlXmlDoc OUTPUT, @xmlText

SELECT OrderID, Freight
FROM OPENXML (@hdlXmlDoc, '/RESULTS/Orders', 1)
   WITH (OrderID integer,
         Freight real)
WHERE Freight = 1.21

exec sp_xml_removedocument @hdlXmlDoc

Dissecting the code, we can see one big potential problem. We're not operating on an XML document in a file, but rather the text is just stored in a string variable. This doesn't seem practical because most of the XML text anyone wants to work with resides in text files. You can eliminate this limitation by wrapping your queries that rely on OPENXML in stored procedures and by passing in the XML text from a document as a parameter to the stored procedure. This does require that the calling application, such as a Visual Basic or Java application, first open the XML text file and extract the text. The calling application then calls the stored procedure and passes the text in as a parameter value.

CREATE PROCEDURE spGetOrder
(
   @xmlText varchar(2000),
   @orderID integer
)
AS

DECLARE    @hdlXmlDoc integer

exec sp_xml_preparedocument @hdlXmlDoc OUTPUT, @xmlText

SELECT OrderID, Freight
FROM OPENXML (@hdlXmlDoc, '/RESULTS/Orders', 1)
   WITH (OrderID integer,
         Freight real)
WHERE Freight = 1.21

exec sp_xml_removedocument @hdlXmlDoc

GO

Continuing with our examination of the OPENXML statement, notice that it is being used in the FROM clause of the SQL query as if it were a table. You've probably also noticed that I'm calling two system-stored procedures, sp_xml_preparedocument and sp_xml_removedocument. The first procedure, sp_xml_preparedocument, opens the XML document and prepares it for reading. SQL Server actually reads the entire document structure into memory and holds it there, much as any XML DOM parser would do. In fact, SQL Server is actually using a DOM parser (the Microsoft MSXML parser, to be exact) to parse the XML document.

Once I'm finished using the document, I call the counterpart, sp_xml_removedocument, to close the XML document and free system resources used to hold it in memory. Because SQL Server pulls the entire file into memory upon calling sp_xml_preparedocument and keeps it there in parsed form until you call the cleanup stored procedure, you should avoid opening very large XML files in this manner. And always call the cleanup stored procedure as soon as you are done with the file to free memory as soon as possible.

The final important aspect of the example that you should take note of is the use of XPath to tell SQL Server exactly which portion of the XML document should be represented as a table by the OPENXML keyword. The XPath query selects all of the Orders elements—all rows and attributes—in the document. The WITH statement defines the attributes we want to extract, which will become columns in the SQL result set and the SQL data types of the columns.

The example showed only how to select data from an XML document. You can also use OPENXML in the context of INSERT, UPDATE, and DELETE statements as well. This means you can extract data from XML documents and insert it into a database or update relational data. In the context of a DELETE statement, you can use the set of data provided by OPENXML, such as a list of primary key values, to determine which records in your relational database to delete.