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
4
5
6
7
8
9 '
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.