Introduction
In the previous article I had presented an example of a Sales Order and explained how to pass item details in XML format. I took the example of a Sales Order as it seems to be a very common task which most developers might have come across at least once.
In this article let us see more complex XML formt and see how to extract values from the XML parameter. Please note that we are still talking about XML in SQL Server 2000. SQL Server 2005 has extensive XML support. In the next article I will present the SQL Server 2005 version of our sample sales order processing task.
We are making two significant changes to the previous XML structure. In this example, we are passing item information as attributes of item element. Further, the Header information is also passed as part of the XML data.
Sample Data
The XML data of the current example is given below.
1 <salesOrder orderNumber="100001"customerNumber="JAC001" orderDate="01-01-2007">
2 <lineItems>
3 <itemitemNumber="A001" qty="10"rate="10.5" />
4 <itemitemNumber="A002" qty="20"rate="11" />
5 <itemitemNumber="A003" qty="30"rate="13" />
6 </lineItems>
7 </salesOrder>
Before we write a stored procedure to save the record, let us first play a litlte with the XML and try to extract all the data that we need. First of all, let us try to read the order header information.
1 DECLARE @orderInfo VARCHAR(4000)
2 SET @orderInfo =
3 '<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
4 <lineItems>
5 <item itemNumber="A001" qty="10" rate="10.5" />
6 <item itemNumber="A002" qty="20" rate="11" />
7 <item itemNumber="A003" qty="30" rate="13" />
8 </lineItems>
9 </salesOrder>'
10
11 -- Initialize XML handle
12 DECLARE @hdoc INT
13 EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo
14
15 -- select the records
16 SELECT x.orderNumber, x.customerNumber, x.orderDate
17 FROM OPENXML ( @hdoc, '/salesOrder', 1 ) WITH (
18 orderNumber VARCHAR(20) '@orderNumber',
19 customerNumber VARCHAR(20) '@customerNumber',
20 orderDate DATETIME '@orderDate'
21 ) AS x
22
23 -- Release XML handle
24 EXEC sp_xml_removedocument @hdoc
You will notice two changes in the OPENXML() syntax. First of all the 3rd parameter that we passed is different. This example uses "1" to indicate that we need the value of attributes. Secondly, the attribute names are prefixed with "@". If you run the above sql you will notice that the result will display order header information. Now let us read the order detail information.
1 DECLARE @orderInfo VARCHAR(4000)
2 SET @orderInfo =
3 '<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
4 <lineItems>
5 <item itemNumber="A001" qty="10" rate="10.5" />
6 <item itemNumber="A002" qty="20" rate="11" />
7 <item itemNumber="A003" qty="30" rate="13" />
8 </lineItems>
9 </salesOrder>'
10
11 -- Initialize XML handle
12 DECLARE @hdoc INT
13 EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo
14
15 -- select the records
16 SELECT x.itemNumber, x.qty, x.rate
17 FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item', 1 ) WITH (
18 itemNumber VARCHAR(20) '@itemNumber',
19 qty INT '@qty',
20 rate FLOAT '@rate'
21 ) AS x
22
23 -- Release XML handle
24 EXEC sp_xml_removedocument @hdoc
Run the above sql and you will see that the query results window will display information of 3 items. Now let us look at the stored procedure which inserts the item informaton to the tables. Download the .sql file here.
1 CREATE PROCEDURE [dbo].[SaveSalesOrderII]
2 (
3 @OrderInfo text
4 )
5 AS
6
7 SET NOCOUNT ON
8
9 BEGIN TRAN
10
11 -- Initialize XML handle
12 DECLARE @hdoc INT
13 EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo
14
15 -- Insert data to order header
16 INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)
17 SELECT x.orderNumber, x.orderDate, x.customerNumber
18 FROM OPENXML ( @hdoc, '/salesOrder', 1 ) WITH (
19 orderNumber VARCHAR(20) '@orderNumber',
20 customerNumber VARCHAR(20) '@customerNumber',
21 orderDate DATETIME '@orderDate'
22 ) AS x
23
24 -- Take the IDENTITY of the new record
25 DECLARE @OrderID INT
26 SET @OrderID = SCOPE_IDENTITY()
27
28 -- Insert data to Order Details
29 INSERT INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)
30 SELECT @OrderID, x.ItemNumber, x.Qty, x.Rate
31 FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item', 1 ) WITH (
32 ItemNumber VARCHAR(20) '@itemNumber',
33 Qty INT '@qty',
34 Rate FLOAT '@rate'
35 ) AS x
36
37 -- Release XML handle
38 EXEC sp_xml_removedocument @hdoc
39
40 IF @@ERROR <> 0
41 ROLLBACK TRAN
42 ELSE
43 COMMIT TRAN
Execute the stored procedure.
1 EXECUTE SaveSalesOrderII
2 '<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
3 <lineItems>
4 <item itemNumber="A001" qty="10" rate="10.5" />
5 <item itemNumber="A002" qty="20" rate="11" />
6 <item itemNumber="A003" qty="30" rate="13" />
7 </lineItems>
8 </salesOrder>'
Conclusions
In this workshop we saw a more complex XML processing example. Both the examples we have seen so far try to insert a new order record. Next I will present an example of updating an existing record (if the order already exists).
About the author
Jacob Sebastian is a SQL Server MVP and blogs regulary at http://blog.beyondrelational.com/ on SQL Server and XML related topics. You can find his linkedin profile here.