A Model for Receiving XML Documents

Suppose that the Northwind Traders company allows customers to place orders by sending an XML purchase order over the Web. The order might be posted to an HTTP application, sent in an e-mail, or transmitted in some other fashion. However the document gets there, software at Northwind Traders needs to receive the incoming order and insert the details into the database.

A possible approach to this task would be to have the receiving application pass the XML document to a stored procedure in the database that inserts the order data into the appropriate tables. Figure 7-1 shows this model.

Figure 7.1 - Receiving and inserting an XML document

Passing XML Data to a Stored Procedure

When you write a stored procedure for inserting data from an XML document into the database, you must ensure that the parameter you plan to use to pass the XML data to the procedure is large enough to handle the maximum size of documents you’ll be receiving. The following table shows the relevant data types that you can specify for parameters receiving XML documents:

Data Type	Maximum Size
Char	8000 characters (fixed length)
Varchar	8000 characters (variable length)
nChar	4000 characters (fixed-length Unicode)
nVarchar	4000 characters (variable-length Unicode)
Text	2,147,483,647 characters
nText	1,073,741,823 characters (Unicode)

In most scenarios, you should use Varchar or nVarchar parameters because they’re adequate for most business documents, such as purchase orders or invoices. If you’re developing applications for a business that might exchange multilingual documents, including characters that don’t appear in the installed character set for SQL Server, you should use a Unicode data type, which will help minimize any character conversion issues.

Parsing and Shredding an XML Document

Before you can use the OpenXML function to insert data, the XML document needs to be parsed and mapped to an in-memory tree structure that represents the nodes in the document. You use the sp_xml_preparedocument stored procedure, which reads the document and verifies that it’s a valid XML document. The stored procedure then returns a handle to a node tree that can be used to retrieve data from the elements and attributes in the document.

After the node tree has been created, you can use the OpenXML function to return a rowset containing data in the XML document. The primary use of this functionality is to get XML data into a relational format so that it can be inserted into a table. This process is known as shredding the document. Because most XML documents contain data that maps to multiple tables, the document might need to be shredded several times to insert all of the data.

Figure 7-2 shows the process of parsing and shredding an XML document.

Figure 7.2 - Parsing and shredding an XML document

Cleaning Up

Once a document has been fully processed, you should use the sp_xml_ removedocument stored procedure to reclaim the memory used by the node tree. Getting into the habit of deleting the document tree can help prevent memory shortage problems in your SQL Server application.

The following example shows a stored procedure that inserts data from an XML document into a table. Note the use of sp_xml_preparedocument, OpenXML, and sp_xml_removedocument.

 CREATE PROCEDURE InsertOrder @xmlOrder VARCHAR(2000) AS DECLARE @iTree INTEGER EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder INSERT orders (OrderID, CustomerID, EmployeeID, OrderDate) SELECT * FROM      OPENXML(@iTree, ‘Order’, 1)     WITH (OrderID INTEGER,           CustomerID nCHAR(5),           EmployeeID INTEGER,           OrderDate DATETIME) EXEC sp_xml_removedocument @iTree 

In this procedure, you can see that the syntax for the OpenXML statement includes a number of parameters and clauses. We’ll examine these in detail throughout the rest of this chapter.