Introduction
In the last two articles I had been presenting an example of a Sales Order which
takes an XML string containing the order information. The samples we discussed explained the TSQL code which extracted the values from the XML buffer and inserts the data to the tables. In the first article we had passed values as XML elements. In the second article we saw an example where values are passed as attributes.
In the last two artciles we had accessed data from a single node. In this article we will see how to access the values from different nodes within the same OPENXML() query.
Sample Data
The following is the sample data we had been using for the last two examples. We will use the same sample data for this example too.
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>
Now let us see how to write an OPENXML() query which retrieves the item details
along with the Order Number. Item details are located at the node <lineItems>.
Order Number is stored in the root node named <salesOrder>.
The following TSQL query shows how to retrieve the information from both nodes.
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
*
17
FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
18
orderNumber VARCHAR(20)
'../../@orderNumber',
19
customerNumber VARCHAR(20)
'../../@customerNumber',
20
itemNumber VARCHAR(20)
'@itemNumber',
21
qty INT '@qty',
22
rate FLOAT '@rate'
23 )
AS x
24
25 -- Release
XML handle
26 EXEC
sp_xml_removedocument @hdoc
You will notice that @orderNumber and @customerNumber variables are prefixed with "../../" which indicates that the values are to be taken from 2
nodes above the node specified in the OPENXML() function. Please note that Node
and Attribute names are case sensitive.
Now let us see the stored procedure which uses the above syntax to retrieve values
from the XML buffer and inserts into the table. For the current example let us create
a new version of OrderDetails table which has an additional column: OrderNumber.
Here is the definition of the new table.
7 CREATE
TABLE [dbo].[OrderDetailsIII](
8 [OrderDetailID]
[int] IDENTITY(1,1) NOT NULL,
9 [OrderHeaderID]
[int] NULL,
10 [OrderNumber]
[varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
11 [ItemNumber] [varchar](20)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
12 [Qty] [int] NULL,
13 [Rate] [float]
NULL
14 ) ON
[PRIMARY]
Now let us see the insert statement which takes values from two different nodes
and populates the order table.
52 INSERT
INTO OrderDetailsIII( OrderHeaderID, OrderNumber, ItemNumber, Qty, Rate)
53 SELECT
@OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
54
FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
55
orderNumber VARCHAR(20)
'../../@orderNumber',
56
ItemNumber VARCHAR(20)
'@itemNumber',
57
Qty INT '@qty',
58
Rate FLOAT '@rate'
59 )
AS x
In this example let us add one more functionality. Let us make it capable of handling
insert and update features. First of all we need to check if the
order exists or not. The following TSQL checks for the existence of the order.
1 SELECT
OrderNumber FROM orderHeader
WHERE OrderNumber = (
2
SELECT OrderNumber FROM OPENXML(
@hdoc, '/salesOrder', 1 )
WITH (orderNumber VARCHAR(20) )
3 )
Here is the complete listing of the updated stored procedure. It checks if the order
exists or not. If the order exists then the order header is updated with
new information. Order details are deleted and re-inserted.
1 CREATE
PROCEDURE [dbo].[SaveSalesOrderIII]
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 -- check
if ORDER already exists
16 DECLARE
@OrderID INT
17 DECLARE
@OrderNumber VARCHAR(20)
18
19 SELECT
@orderNumber = x.OrderNumber
20
FROM OPENXML( @hdoc, '/salesOrder',
1 ) WITH (orderNumber VARCHAR(20)
) AS x
21
22 SELECT
@OrderID = OrderID
23
FROM orderHeader WHERE OrderNumber =
@OrderNumber
24
25 -- Insert/update
order header
26 IF
@OrderID IS NULL BEGIN
27
-- if order does not exist, insert it.
28
INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)
29
SELECT x.orderNumber, x.orderDate, x.customerNumber
30
FROM OPENXML ( @hdoc, '/salesOrder',
1 ) WITH (
31
orderNumber VARCHAR(20)
'@orderNumber',
32
customerNumber VARCHAR(20)
'@customerNumber',
33
orderDate DATETIME
'@orderDate'
34
) AS x
35
SET @OrderID = SCOPE_IDENTITY()
36 END ELSE
BEGIN
37
-- if the order exists, update it.
38
UPDATE orderHeader SET
39
customerNumber = x.customerNumber,
40
orderDate = x.orderDate
41
FROM orderHeader h
42
INNER JOIN OPENXML ( @hdoc, '/salesOrder',
1 ) WITH (
43
orderNumber VARCHAR(20)
'@orderNumber',
44
customerNumber VARCHAR(20)
'@customerNumber',
45
orderDate DATETIME
'@orderDate'
46
) AS x ON (x.orderNumber
= h.orderNumber )
47 END
48
49 -- delete
previous item details records, if exists
50 DELETE
FROM orderDetailsIII WHERE OrderHeaderID
= @OrderID
51
52 -- Insert
data to Order Details
53 INSERT
INTO OrderDetailsIII( OrderHeaderID, OrderNumber, ItemNumber, Qty, Rate)
54 SELECT
@OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
55
FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
56
orderNumber VARCHAR(20)
'../../@orderNumber',
57
ItemNumber VARCHAR(20)
'@itemNumber',
58
Qty INT '@qty',
59
Rate FLOAT '@rate'
60 )
AS x
61
62 -- Release
XML handle
63 EXEC
sp_xml_removedocument @hdoc
64
65 IF
@@ERROR <> 0
66
ROLLBACK TRAN
67 ELSE
68
COMMIT TRAN
Execute the stored procedure by running the following TSQL
1 EXECUTE
SaveSalesOrderIII
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>'
You will notice that the first time you run the code, a new record is inserted to
the order header table and order details table. When you execute it subsequently
the order header is updated and order details are deleted and inserted again.
Deleting the order details and re-inserting the records is pretty simply and easy
to do. It works for most of the cases. Take the case of a warehouse where order
details table has a column QtyPicked which stores the number of cases already
picked. If the user edits an order we canot delete the item details and insert the
records again. If we do so, we will loose the additional data which is not updted/inserted
from the Sales Order Entry. QtyPicked may be updated from the Picking
module. So Order Entry should not alter the value.
There are 3 changes that can happen to the item details.
- One or more new items are added
- One or more existing items are modified
- One or more existing items are deleted.
Here is the script of the new version of Order detail table.
1 CREATE
TABLE [dbo].[OrderDetailsIV](
2 [OrderDetailID]
[int] IDENTITY(1,1) NOT NULL,
3 [OrderHeaderID]
[int] NULL,
4 [OrderNumber]
[varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
5 [ItemNumber] [varchar](20)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
6 [Qty] [int] NULL,
7 [Rate] [float]
NULL,
8 [PickedQty] [int]
NULL
9 ) ON
[PRIMARY]
The stored procedure should essentially do the following.
1. Delete the records which exists in the table but not there in the XML buffer
55 -- delete
any items which does not exist in the XML but exists in the table
56 DELETE
FROM OrderDetailsIV
57
WHERE OrderHeaderID = @OrderID
58
AND ItemNumber NOT IN (
59
SELECT x.itemNumber
60
FROM OPENXML ( @hdoc,
'/salesOrder/lineItems/item', 1 )
WITH (
61
ItemNumber VARCHAR(20)
'@itemNumber'
62
) AS
x )
2. Update the records which exists in both the table and in the XML buffer
64 -- Update
any existing records
65 UPDATE
OrderDetailsIV SET
66 Qty = x.Qty,
67 Rate = x.Rate
68 FROM
OrderDetailsIV o
69 INNER
JOIN OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
70
orderNumber VARCHAR(20)
'../../@orderNumber',
71
ItemNumber VARCHAR(20)
'@itemNumber',
72
Qty INT '@qty',
73
Rate FLOAT '@rate'
74 )
AS x ON (o.orderNumber = x.orderNumber
AND o.itemNumber = x.itemNumber )
3. Insert new records (which exists in XML buffer and not in the table)
77 -- Insert
new records
78 INSERT
INTO OrderDetailsIV( OrderHeaderID, OrderNumber, ItemNumber, Qty, Rate)
79 SELECT
@OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
80
FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
81
orderNumber VARCHAR(20)
'../../@orderNumber',
82
ItemNumber VARCHAR(20)
'@itemNumber',
83
Qty INT '@qty',
84
Rate FLOAT '@rate'
85 )
AS x
86 WHERE
x.itemNumber NOT IN (
87
SELECT itemNumber FROM OrderDetailsIV
WHERE orderHeaderID = @orderID )
Here is the complete listing of the updated stored procedure.
1 CREATE
PROCEDURE [dbo].[SaveSalesOrderIV]
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 -- check
if ORDER already exists
16 DECLARE
@OrderID INT
17 DECLARE
@OrderNumber VARCHAR(20)
18
19 SELECT
@orderNumber = x.OrderNumber
20
FROM OPENXML( @hdoc, '/salesOrder',
1 ) WITH (orderNumber VARCHAR(20)
) AS x
21
22 SELECT
@OrderID = OrderID
23
FROM orderHeader WHERE OrderNumber =
@OrderNumber
24
25 -- Insert/update
order header
26 IF
@OrderID IS NULL BEGIN
27
INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)
28
SELECT x.orderNumber, x.orderDate, x.customerNumber
29
FROM OPENXML ( @hdoc, '/salesOrder',
1 ) WITH (
30
orderNumber VARCHAR(20)
'@orderNumber',
31
customerNumber VARCHAR(20)
'@customerNumber',
32
orderDate DATETIME
'@orderDate'
33
) AS x
34
SET @OrderID = SCOPE_IDENTITY()
35 END ELSE
BEGIN
36
UPDATE orderHeader SET
37
customerNumber = x.customerNumber,
38
orderDate = x.orderDate
39
FROM orderHeader h
40
INNER JOIN OPENXML ( @hdoc, '/salesOrder',
1 ) WITH (
41
orderNumber VARCHAR(20)
'@orderNumber',
42
customerNumber VARCHAR(20)
'@customerNumber',
43
orderDate DATETIME
'@orderDate'
44
) AS x ON (x.orderNumber
= h.orderNumber )
45 END
46
47 -- delete
any items which does not exist in the XML but exists in the table
48 DELETE
FROM OrderDetailsIV
49
WHERE OrderHeaderID = @OrderID
50
AND ItemNumber NOT IN (
51
SELECT x.itemNumber
52
FROM OPENXML ( @hdoc,
'/salesOrder/lineItems/item', 1 )
WITH (
53
ItemNumber VARCHAR(20)
'@itemNumber'
54
) AS
x )
55
56 -- Update
any existing records
57 UPDATE
OrderDetailsIV SET
58 Qty = x.Qty,
59 Rate = x.Rate
60 FROM
OrderDetailsIV o
61 INNER
JOIN OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
62
orderNumber VARCHAR(20)
'../../@orderNumber',
63
ItemNumber VARCHAR(20)
'@itemNumber',
64
Qty INT '@qty',
65
Rate FLOAT '@rate'
66 )
AS x ON (o.orderNumber = x.orderNumber
AND o.itemNumber = x.itemNumber )
67
68
69 -- Insert
new records
70 INSERT
INTO OrderDetailsIV( OrderHeaderID, OrderNumber, ItemNumber, Qty, Rate)
71 SELECT
@OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
72
FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item',
1 ) WITH (
73
orderNumber VARCHAR(20)
'../../@orderNumber',
74
ItemNumber VARCHAR(20)
'@itemNumber',
75
Qty INT '@qty',
76
Rate FLOAT '@rate'
77 )
AS x
78 WHERE
x.itemNumber NOT IN (
79
SELECT itemNumber FROM OrderDetailsIV
WHERE orderHeaderID = @orderID )
80
81 -- Release
XML handle
82 EXEC
sp_xml_removedocument @hdoc
83
84 IF
@@ERROR <> 0
85
ROLLBACK TRAN
86 ELSE
87
COMMIT TRAN
Execute the following TSQL and you will see that 3 new records are added to OrderDetailsIV.
1 EXECUTE
SaveSalesOrderIV
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>'
Now execute the following statement.
1 EXECUTE
SaveSalesOrderIV
2 '<salesOrder
orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
3
<lineItems>
4
<item itemNumber="A001" qty="12" rate="11" />
5
<item itemNumber="A003" qty="30" rate="13" />
6
<item itemNumber="A004" qty="35" rate="14.5" />
7
<item itemNumber="A005" qty="50" rate="17" />
8
</lineItems>
9 </salesOrder>'
After the statement is executed you will notice that Item "A002" is deleted from
the Item details table. Again, Two new items are added and item "A001" is updated
with new rate and qty.
Conclusions
In this article
we have seen more advanced XML processing. In the next article we
will see the SQL Server 2005 version of the above stored procedure. SQL Server 2005
introduced a new data type XML which provides much more powerful XML processing.