Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sales Order Workshop Part III

By Jacob Sebastian,

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.
  1. One or more new items are added
  2. One or more existing items are modified
  3. 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.

Total article views: 6664 | Views in the last 30 days: 6
 
Related Articles
FORUM

Dynamic Order by -need both Dates and Varchars

Ordering with both Dates and Varchars

FORUM

varchar issue

varchar issue

ARTICLE

Sales Order Workshop Part II

In part 2 of this series, Jacob Sebastion continues looking at XML in SQL Server 2000 with some adva...

ARTICLE

Sales Order Workshop Part IV

In the fourth installment of this series, Jacob Sebastian moves on to SQL Server 2005 and the new XM...

FORUM

sort order

sort order

Tags
miscellaneous    
stored procedures    
t-sql    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones