Processing the XML with OpenXML
Now, you're ready to use OpenXML to process the OrderForm component's XML. First, let's look at the spOpenXML_PlaceOrder stored procedure in Web Listing 3, which contains the OpenXML statements that process the order data in the XML document.
First, spOpenXML_PlaceOrder creates a local in-memory representation of the XML that the data-tier component passes to it, as callout A in Web Listing 3 shows. At callout B, the procedure begins a transaction, then uses OpenXML to insert a row into the OrderHeader table. Note that because the table column names don't match the XML document node names, you have to use the XPath column-pattern parameter, DICTITEM[@key="order_id"]/VALUE. The column-pattern parameter gives OpenXML the information it needs to appropriately map column names to XML document nodes. Also note that because the /ROOT/DICTIONARY/DICTITEM path isn't repeated in the local XML document, OpenXML returns only one header row from this SELECT statement.
At callout C, the stored procedure uses another OpenXML statement to populate the OrderDetail table. This statement's structure is nearly identical to that of the statement that populated the OrderHeader table. Note, however, that because /ROOT/DICTIONARY/DICTITEM/VALUE/SIMPLELIST/LISTITEM/VALUE/DICTIONARY repeats in the XML document, this statement returns more than one row of data. You can see the power of combining the OpenXML rowset function with an INSERT, UPDATE, or DELETE statement. With one call to OpenXML, you can retrieve multiple rows of data from an XML document and store that data in a table, a local T-SQL variable of type TABLE, and so on. After extracting the data you need from the local XML document, you no longer need access to the document. You can remove the XML document from memory by using the system stored procedure sp_xml_removedocument, as callout D shows. Figure 4 shows the resulting data in the two permanent SQL Server tables OrderHeader and OrderDetail.
Final Notes
Voilà! You've now seen how to pass a structured, hierarchical set of rows from an application to a SQL Server 2000 database. You've also seen how to populate multiple database tables—all in one call to the database—by sending one simple text input parameter to a stored procedure. To make the sample application relatively simple, I limited the data sent to the database to just a few rows. You can see the power and efficiency of this technique even more when you're working with complex applications.
Packaging hierarchical or variable-size data into an XML document, then sending the XML to SQL Server gives you two main benefits: increased efficiency because of fewer network round- trips and a data tier that's conceptually simple and easy to code and test. The increased efficiency you gain by limiting the number of round-trips between the application and SQL Server might not be apparent where the volume of data exchange is relatively low. Web and client/server architectures typically call for a high-speed connection between the application and SQL Server anyway. But in high-volume situations and environments that use low-speed connectivity between the application and SQL Server, you'll see significant performance advantages of using XML to write data to SQL Server.
However, the second benefit—an easy-to-code data tier—is just as important as the first. By sending XML to SQL Server as a single input parameter to a stored procedure, the data tier no longer needs to be aware of the underlying database structure. That knowledge now lies in the stored procedures that use OpenXML to map XML nodes to tables, rows, and columns. Even in this article's simple example, the data-tier components had to call at least two stored procedures to insert a new order. The components had to know in what order to call these stored procedures and what parameters to pass to each. In addition, the components had to call the stored procedure responsible for inserting the order-detail rows a variable number of times, depending on how many line items the order contained. But if you package the data as XML and use OpenXML to map the data into SQL Server, the data tier needs to know only the one stored procedure that inserts an order and the one input parameter that this procedure uses.
End of Article
Prev. page
1
2
3
[4]
next page -->
