Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML-based Business Objects with SQL Server and .NET (Part II) : Page 2

Improve performance and gain convenience by using custom business objects serialized to and from XML rather than forcing your presentation-tier developers to work directly with DataSets.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Processing XML Data with T-SQL
To implement the technique, you must first write a stored procedure that accepts as argument an XML document resenting an order and commits the order to the database. The stored procedure isn't difficult to write, if you know how to handle an XML document with T-SQL. There are basically two keywords you need to know, the sp_xml_prepareddocument built-in stored procedure and the OpenXML function. If you're familiar with SQL Server's XML extensions, you already know how to commit a serialized order to the database.

The sp_xml_prepareddocument built-in stored procedure loads an XML document into memory. After the document has been laid out in memory, you can use the OpenXML function to retrieve specific elements from the document using XPath syntax. You need to create a stored procedure that accepts a serialized Order object argument and inserts the order's data into the proper tables. This example uses the Orders class developed in the Part I of this article to read data out of SQL Server and into an instance of the Orders class. The stored procedure's name is SaveXMLOrder. I'll explain how to parse and process the XML data with T-SQL as we go along. The stored procedure accepts a single argument, of type nText. Here's a step-by-step breakdown of the stored procedure, starting with the declaration.

CREATE PROCEDURE SaveXMLOrder @Order nText AS ...

The sp_xml_prepareddocument stored procedure accepts two arguments: an Integer variable, where it will store the handle to the in-memory representation of the parsed document, and the string with the XML data. Declare the handler to the document and call the built-in stored procedure as shown here:

... DECLARE @XMLHandler int EXEC sp_xml_preparedocument @XMLHandler output, @Order IF @@Error<>0 BEGIN RETURN(--1) END ...

The next step is to read specific elements and/or attributes of the XML document and insert the appropriate rows into the Orders and Order Details tables. This must take place in the context of a transaction, of course. First you must extract the order's header (the customer's ID, the order's date and freight) from the XML document. To do so, use a SELECT statement, as usual, with one difference: in the statement's FROM clause, you call the OpenXML function. The OpenXML function accepts as arguments a handler to an XML document and an XPath expression that retrieves the desired nodes from the XML document (instead of one or more tables, as you would do with a regular SQL statement). XPath is to hierarchical (XML) data what SQL is to relational data. It's a language for retrieving the nodes of an XML document, whereas SQL retrieves columns. The following SELECT statement returns the order's header:

... SELECT CustomerID, CAST(OrderDate As datetime), CAST(Freight AS money) FROM OpenXML(@XMLHandler,'/Orders', 1) WITH (CustomerID nchar(5) '@CustomerID', OrderDate varchar(10) '@OrderDate', Freight varchar(12) '@Freight') ...

The columns of the row(s) returned by the OpenXML function correspond to the attributes of the element(s) selected. Using the optional WITH clause you can specify the description of the result set to be generated and how the processor should map XML nodes to the result set's columns. After defining the XML element mapped to a row, you must decide how the element's attributes will be mapped to columns. You do that in the WITH part of the statement. You specify each column's name and type, as well as the name of the attribute to be mapped to the specific column. When the attributes have the same names as the matching columns, you can omit the names of the attributes and write the WITH clause as follows:

... WITH (CustomerID nchar(5), OrderDate varchar(10), Freight varchar(12)) ...

Now that you've selected the XML nodes containing the header information, you can insert a row into the Orders table using the following INSERT INTO statement:

... INSERT INTO Orders (CustomerID , OrderDate) SELECT CustomerID, CAST(OrderDate As datetime) FROM OpenXML(@XMLHandler,'/Orders', 1) WITH (CustomerID nchar(5) , OrderDate varchar(10)) IF @@Error<>0 BEGIN ROLLBACK TRANSACTION RETURN(--2) END ...

If the argument passed to the stored procedure doesn't have the correct structure, or if the new row can't be inserted into the Orders table for any reason, the stored procedure aborts the transaction and returns the value —2 to indicate that it failed to insert the header.

After adding the order's header to the Orders table, the procedure extracts the ID assigned to the new order by SQL Server and stores it to a local variable, the @OrderID variable. Next, it adds the detail lines to the Order Details table. The process is the same: use an INSERT INTO statement that retrieves the columns of all the detail lines from the XML document with a single SELECT statement and insert them into the Orders table. This time we pass the Order/Detail XPath expression to retrieve the Detail elements as the argument to the OpenXML function. The INSERT INTO statement inserts all the rows returned by that expression into the Order Details table. Notice that the procedure sets the value of the OrderID column to @OrderID for all inserted rows. Here's the INSERT statement that inserts the detail lines:

... INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) SELECT @OrderID,ProductID, ProductPrice, ProductQTY, Discount FROM OpenXML(@XMLHandler,'/Orders/Detail') WITH (ProductID int '@ProductID', ProductPrice money '@Price', ProductQTY smallint '@Quantity', Discount real '@Discount') ...

After inserting the detail lines you can commit the transaction and return the new order's ID. If any operation fails, the transaction is rolled back and the stored procedure returns a negative value.

Listing 1 contains the complete listing of the SaveXMLOrder stored procedure. Notice that it returns a negative value to indicate a failure and that different conditions return different values. If all goes well and the database commits the transaction successfully, the stored procedure returns a positive numeric value—the ID of the new order.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date