Sales Order Workshop Part III

,

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.

Rate

5 (2)

Share

Share

Rate

5 (2)