Introduction
In the last few articles we had been trying to look into the example of a Sales Order entry and had been analysing how to pass the order data as an
XML buffer to the stored procedure. We had been using OPENXML() to generate a ROWSET from the XML data buffer passed into the stored procedure.
Today we will look into using the XML Native data type introduced by SQL Server 2005. This article explains how to rewrite the previous examples
using the XML data type introduced by SQL Server 2005.
You can find the previous articles in this series here (1), here (2) and here (3).
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>
First of all, let us see how to extract the information from the XML variable.
The following TSQL query extracts order header information
from the XML variable.
1 DECLARE
@x XML
2 SET
@x = '
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 SELECT
12 x.header.value('@orderNumber[1]', 'varchar(20)')
AS OrderNumber,
13 x.header.value('@customerNumber[1]', 'varchar(20)')
AS CustomerNumber,
14 x.header.value('@orderDate[1]', 'datetime')
AS OrderDate
15 FROM
@x.nodes('//salesOrder')
AS x(header)
Next, let us see how to read the item detail information.
11 SELECT
12 x.item.value('@itemNumber[1]','varchar(10)')
AS ItemNumber,
13 x.item.value('@qty[1]','int')
AS Qty,
14 x.item.value('@rate[1]','float')
AS Rate
15 FROM
@x.nodes('//item') AS
x(item)
Well, we are now able to read information from the XML variable.
Now let us start
rewriting the previous stored procedure. First of all we need to change the data type of the parameter
taken by the stored procedure. The previous version took a variable of TEXT data type. The new version
of the stored procedure will take a variable of XML data type.
A complete listing of the updated stored procedure is given below. Comments
given inline explain the changes we added since the last version. It also points to the new TSQL keywords used
in the code.
1 ALTER
PROCEDURE [dbo].[SaveSalesOrderV]
2 (
3
/*
4
In this version we are using XML data type, instead of TEXT.
5
*/
6 @OrderInfo XML
7 )
8 AS
9
10 SET NOCOUNT
ON
11
12 /*
13
The next point that needs attention is that we are using Structured Exception Handling
14
capability of SQL Server 2005. The code below is within a
TRY..CATCH block. In the previous
15
version we used @@ERROR system global variable to detect errors.
16 */
17 BEGIN
TRY
18
BEGIN TRAN
19
20
/*
21
We dont need the XML document handle any more.
22
23
DECLARE @hdoc INT
24
EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo
25
*/
26
27
-- check if ORDER already exists
28
DECLARE @OrderID INT
29
DECLARE @OrderNumber VARCHAR(20)
30
31
/*
32
SELECT @orderNumber = x.OrderNumber
33
FROM OPENXML( @hdoc, '/salesOrder', 1 ) WITH
(orderNumber VARCHAR(20) ) AS x
34
35
We will no more use OPENXML to access the information stored
inside the XML variable.
36
The code below uses XQuery retrieve the information from
the order information.
37
*/
38
39
SELECT @orderNumber = x.header.value('@orderNumber[1]',
'varchar(20)')
40
FROM @OrderInfo.nodes('//salesOrder')
as x(header)
41
42
SELECT @OrderID = OrderID
43
FROM orderHeader WHERE
OrderNumber = @OrderNumber
44
45
/*
46
If the order does not exist, insert it.
47
Once again, we are using XQuery to retrieve information from
the XML variable.
48
*/
49
50
IF @OrderID IS NULL BEGIN
51
INSERT INTO OrderHeader (OrderNumber, CustomerNumber, OrderDate)
52
SELECT
53
x.header.value('@orderNumber[1]',
'varchar(20)') as
OrderNumber,
54
x.header.value('@customerNumber[1]',
'varchar(20)') as
customerNumber,
55
x.header.value('@orderDate[1]',
'datetime') as
OrderDate
56
FROM @OrderInfo.nodes('//salesOrder')
as x(header)
57
58
SET @OrderID = SCOPE_IDENTITY()
59
END ELSE BEGIN
60
/*
61
Here, we are using yet another new feature
of SQL Server 2005, CTE. In the code below
62
it is not necessary to use a Common Table Expression
(CTE). However, I used it for
63
making the code simpler to read and understand.
64
*/
65
;WITH orderInfo AS
66
(
67
SELECT
68
x.header.value('@orderNumber[1]',
'varchar(20)') AS
OrderNumber,
69
x.header.value('@customerNumber[1]',
'varchar(20)') AS
CustomerNumber,
70
x.header.value('@orderDate[1]',
'datetime') AS
OrderDate
71
FROM @OrderInfo.nodes('//salesOrder')
AS x(header)
72
)
73
74
UPDATE orderHeader SET
75
customerNumber = x.CustomerNumber,
76
orderDate = x.OrderDate
77
FROM orderHeader h
78
INNER JOIN orderInfo x ON
(x.OrderNumber = h.OrderNumber)
79
END
80
81
/*
82
delete any items which does not exist in the XML but exists
in the table.
83
Again, uses XQuery to access the information inside the XML
variable.
84
*/
85
DELETE FROM OrderDetailsIV
86
WHERE OrderHeaderID = @OrderID
87
AND ItemNumber NOT IN
88
(
89
SELECT
90
x.item.value('@itemNumber[1]','varchar(10)') AS
ItemNumber
91
FROM
@OrderInfo.nodes('//item')
AS x(item)
92
)
93
94
/*
95
Update any existing records.
96
Again, we are using another new KEYWORD introduced with SQL
Server 2005. Note the usage of
97
CROSS APPLY, which returns a resultset comprising of the
"Order Number" from the order header
98
element and other item information from the item details
part of the XML structure.
99
*/
100 ;WITH
itemInfo AS(
101 SELECT
102
y.header.value('@orderNumber[1]', 'varchar(20)') as
OrderNumber,
103
x.item.value('@itemNumber[1]','varchar(10)')
AS ItemNumber,
104
x.item.value('@qty[1]','int')
AS Qty,
105
x.item.value('@rate[1]','float')
AS Rate
106 FROM @OrderInfo.nodes('//item')
AS x(item)
107 CROSS APPLY @OrderInfo.nodes('//salesOrder')
AS y(header)
108 )
109
UPDATE OrderDetailsIV SET
110 Qty =
x.Qty,
111 Rate
= x.Rate
112
FROM OrderDetailsIV o
113
INNER JOIN itemInfo x ON (x.OrderNumber
= o.OrderNumber AND o.ItemNumber = x.ItemNumber)
114
115
/*
116 Insert new records.
117 Note usage of CTE, CROSS APPLY and XQuery.
118
*/
119 ;WITH
itemInfo AS(
120 SELECT
121
y.header.value('@orderNumber[1]', 'varchar(20)') as
OrderNumber,
122
x.item.value('@itemNumber[1]','varchar(10)')
AS ItemNumber,
123
x.item.value('@qty[1]','int')
AS Qty,
124
x.item.value('@rate[1]','float')
AS Rate
125 FROM @OrderInfo.nodes('//item')
AS x(item)
126 CROSS APPLY @OrderInfo.nodes('//salesOrder')
as y(header)
127 )
128
INSERT INTO OrderDetailsIV( OrderHeaderID, OrderNumber, ItemNumber, Qty,
Rate)
129
SELECT @OrderID, x.orderNumber, x.ItemNumber, x.Qty, x.Rate
130 FROM itemInfo AS
x
131 WHERE x.itemNumber NOT IN
(
132
SELECT itemNumber
FROM OrderDetailsIV WHERE orderHeaderID
= @orderID )
133
134
COMMIT TRANSACTION
135 END
TRY
136 BEGIN
CATCH
137
/*
138 Rollback the transaction in case of an error.
139
*/
140
IF XACT_STATE() <> 0
141
BEGIN
142 ROLLBACK TRANSACTION
143
END
144 END
CATCH
145
Conclusions
In this article we have seen how to work with XML data type. We saw how to retrieve values using XQuery.
We further, saw a few features/keywords introduced by SQL Server 2005. We say Structured Exception Handling,
Common Table Expressions, XQuery, and the CROSS APPLY operator.
