Sales Order Workshop Part IV

,

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.

Rate

5 (7)

Share

Share

Rate

5 (7)