SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Sales Order Workshop Part IV

By Jacob Sebastian,


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>'


   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




   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 */


   18     BEGIN TRAN


   20     /*

   21         We dont need the XML document handle any more.


   23         DECLARE @hdoc INT   

   24         EXEC sp_xml_preparedocument @hdoc OUTPUT, @OrderInfo

   25     */


   27     -- check if ORDER already exists

   28     DECLARE @OrderID INT

   29     DECLARE @OrderNumber VARCHAR(20)


   31     /*

   32         SELECT @orderNumber = x.OrderNumber

   33             FROM OPENXML( @hdoc, '/salesOrder', 1 ) WITH (orderNumber VARCHAR(20) ) AS x


   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     */


   39     SELECT @orderNumber = x.header.value('@orderNumber[1]', 'varchar(20)')

   40         FROM @OrderInfo.nodes('//salesOrder') as x(header)


   42     SELECT @OrderID = OrderID

   43         FROM orderHeader WHERE OrderNumber = @OrderNumber


   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     */


   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)


   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         )


   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


   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             )


   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)


  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 )



  135 END TRY


  137     /*

  138         Rollback the transaction in case of an error.

  139     */

  140     IF XACT_STATE() <> 0

  141     BEGIN


  143     END




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.

Total article views: 12315 | Views in the last 30 days: 14
Related Articles

varchar issue

varchar issue


Tricky ...VARCHAR

VARCHAR logics


Sales Order Workshop Part III

In the previous articles, Jacob Sebastian looked at using XML to save a sales order with variable nu...


Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)


Cross apply / XML shredding -- mutiple nodes creating problems

Oh XML Gurus please help. I don't have a clear understanding of CROSS APPLY.  I have the followi...

sql server 2005    
stored procedures