SQLServerCentral Article

Sales Order Workshop Part II

,

Introduction

In the previous article I had presented an example of a Sales Order and explained how to pass item details in XML format. I took the example of a Sales Order as it seems to be a very common task which most developers might have come across at least once.

In this article let us see more complex XML formt and see how to extract values from the XML parameter. Please note that we are still talking about XML in SQL Server 2000. SQL Server 2005 has extensive XML support. In the next article I will present the SQL Server 2005 version of our sample sales order processing task.

We are making two significant changes to the previous XML structure. In this example, we are passing item information as attributes of item element. Further, the Header information is also passed as part of the XML data.

Sample Data

The XML data of the current example is given below.

    1 <salesOrder orderNumber="100001"customerNumber="JAC001" orderDate="01-01-2007">

    2   <lineItems>

    3     <itemitemNumber="A001" qty="10"rate="10.5" />

    4     <itemitemNumber="A002" qty="20"rate="11" />

    5     <itemitemNumber="A003" qty="30"rate="13" />

    6   </lineItems>

    7 </salesOrder>

Before we write a stored procedure to save the record, let us first play a litlte with the XML and try to extract all the data that we need. First of all, let us try to read the order header information. 

    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 x.orderNumber, x.customerNumber, x.orderDate

   17     FROM OPENXML ( @hdoc, '/salesOrder', 1 ) WITH (

   18         orderNumber VARCHAR(20) '@orderNumber',

   19         customerNumber VARCHAR(20) '@customerNumber',

   20         orderDate DATETIME '@orderDate'

   21     ) AS x

   22 

   23 -- Release XML handle

   24 EXEC sp_xml_removedocument @hdoc

 

You will notice two changes in the OPENXML() syntax. First of all the 3rd parameter that we passed is different. This example uses "1" to indicate that we need the value of attributes. Secondly, the attribute names are prefixed with "@". If you run the above sql you will notice that the result will display order header information. Now let us read the order detail information. 

    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 x.itemNumber, x.qty, x.rate

   17     FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item', 1 ) WITH (

   18         itemNumber VARCHAR(20) '@itemNumber',

   19         qty INT '@qty',

   20         rate FLOAT '@rate'

   21     ) AS x

   22 

   23 -- Release XML handle

   24 EXEC sp_xml_removedocument @hdoc

Run the above sql and you will see that the query results window will display information of 3 items. Now let us look at the stored procedure which inserts the item informaton to the tables. Download the .sql file here.

    1 CREATE PROCEDURE [dbo].[SaveSalesOrderII]

    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 -- Insert data to order header

   16 INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)    

   17 SELECT x.orderNumber, x.orderDate, x.customerNumber

   18     FROM OPENXML ( @hdoc, '/salesOrder', 1 ) WITH (

   19         orderNumber VARCHAR(20) '@orderNumber',

   20         customerNumber VARCHAR(20) '@customerNumber',

   21         orderDate DATETIME '@orderDate'

   22     ) AS x

   23 

   24 -- Take the IDENTITY of the new record

   25 DECLARE @OrderID INT

    26 SET @OrderID = SCOPE_IDENTITY()

    27 

   28 -- Insert data to Order Details

   29 INSERT INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)

   30 SELECT @OrderID, x.ItemNumber, x.Qty, x.Rate

   31     FROM OPENXML ( @hdoc, '/salesOrder/lineItems/item', 1 ) WITH (

   32         ItemNumber VARCHAR(20) '@itemNumber',

   33         Qty INT '@qty',

   34         Rate FLOAT '@rate'

    35     ) AS x

    36 

   37 -- Release XML handle

    38 EXEC sp_xml_removedocument @hdoc

    39 

    40 IF @@ERROR <> 0

    41     ROLLBACK TRAN

   42 ELSE

   43     COMMIT TRAN

Execute the stored procedure.

    1 EXECUTE SaveSalesOrderII

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

Conclusions

In this workshop we saw a more complex XML processing example. Both the examples we have seen so far try to insert a new order record. Next I will present an example of updating an existing record (if the order already exists).

About the author

Jacob Sebastian is a SQL Server MVP and blogs regulary at http://blog.beyondrelational.com/ on SQL Server and XML related topics. You can find his linkedin profile here.

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating