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

Sales Order Workshop Part II

By Jacob Sebastian,

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.

Total article views: 7249 | Views in the last 30 days: 4
 
Related Articles
ARTICLE

Sales Order Workshop Part III

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

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

ARTICLE

Saving a Sales Order Part 1

How many times have you tried to save a sales order to your database? For many DBAs this is a common...

FORUM

Sales Order Workshop Part IV

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSeb...

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