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
<item
itemNumber="A001"
qty="10" rate="10.5" />
4
<item
itemNumber="A002"
qty="20" rate="11" />
5
<item
itemNumber="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.orderNumber, x.customerNumber, x.orderDate
17
FROM OPENXML
( @hdoc, '/salesOrder/lineItems/item',
1 )
WITH (
18
orderNumber VARCHAR(20)
'@itemNumber',
19
customerNumber INT '@qty',
20
orderDate 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).
