Introduction
This article tries to look into a common scenario of saving a sales order which, many of us might have done over a few hundred times. What is special about a Sales Order Entry? Well, a Sales Order has a fixed header infrmation and a variable number of item details. A Sales Order may have 3 lines, 10 lines or 500 lines.
I have seen applications where a stored procedure is called to save the header information, then another stored procedure is called to save each line details. So if the Sales Order has 75 lines, a procedure is called 75 times to save each line. Though this looks the easiest solution, it is not advisable to do so for performance reasons. For best performance, it is advisable to call only a single procedure and only once during any given operation. So what is the next option?
Some of the applications I have seen in the past were found to be passing line details in a comma separated string. The application will create such a string and will pass it to the stored procedure. the stored procedure will then parse the string, run a loop and save each item. This works well, but not very efficient.
This approach has two major draw backs:
- SQL Server 2000 restricts VARCHAR to 8000 characters and NVARCHAR to 4000 characters. If the Sales Order has a large number of line items and each item has lots of information, 8000 characters may not be enough to pass the entire data. (Note: SQL Server 2005 supports upto 2^31 characters for VARCHAR and 2^30 characters for NVARCHAR).
- While saving the record, there needs to be a loop which parses the string and iterates over each line item. Each item has to be saved with an INSERT statement and the INSERT statement has to be fired N times, where N is the number of items in the Sales Order.
This article explains how to handle the above scenario by passing data in XML format.
Sample Data
For the purpose of this example, let us take a sample order with 3 line items. The application will create an XML buffer as given below, containing the details of the items entered by the user.
1 <lineItems>
2 <item>
3 <itemNumber>A001itemNumber>
4 <qty>10qty>
5 <rate>10.5rate>
6 item>
7 <item>
8 <itemNumber>A002itemNumber>
9 <qty>20qty>
10 <rate>11rate>
11 item>
12 <item>
13 <itemNumber>A003itemNumber>
14 <qty>30qty>
15 <rate>13rate>
16 item>
17 lineItems>
Now let us have a look at the stored procedure which accepts the above data and inserts it to the database.
1 CREATE PROCEDURE [dbo].[SaveSalesOrder]
2 (
3 @OrderNumber VARCHAR(10),
4 @OrderDate DATETIME,
5 @CustomerNumber VARCHAR(10),
6 @LineItems text
7 )
Parsing the XML string involves a 3 step process. The first step is to create a handle that can be used to parse the XML buffer.
12 DECLARE @hdoc INT
13 EXEC sp_xml_preparedocument @hdoc OUTPUT, @LineItems
The next step is to use OPENXML() to access the data in the XML buffer as a table.
28 INSERT INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)
29 SELECT @OrderID, x.ItemNumber, x.Qty, x.Rate
30 FROM OPENXML ( @hdoc, '/lineItems/item', 2 ) WITH (
31 ItemNumber VARCHAR(20) 'itemNumber',
32 Qty INT 'qty',
33 Rate FLOAT 'rate'
34 ) AS x
In the above code, the function OPENXML() returns a table containing the data we passed in the XML buffer and we can directly insert it to the order detail table. The next step is to release the XML handle we created earlier.
37 EXEC sp_xml_removedocument @hdoc
The above statement releases the XML handle. It is very important that you release the XML handle. If you forget to do it, you will run out of system resources if the stored procedure is called repeatedly.
Source listing
A complete listing of the source code is given below:
Sample Tables
Order Header
Order Details
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 SET ANSI_PADDING ON
6 GO
7 CREATE TABLE [dbo].[OrderHeader](
8 [OrderID] [int] IDENTITY(1,1) NOT NULL,
9 [OrderNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
10 [OrderDate] [datetime] NULL,
11 [CustomerNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
12 ) ON [PRIMARY]
13
14 GO
15
16 CREATE TABLE [dbo].[OrderDetails](
17 [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
18 [OrderHeaderID] [int] NULL,
19 [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
20 [Qty] [int] NULL,
21 [Rate] [float] NULL
22 ) ON [PRIMARY]
23
24 GO
25 SET ANSI_PADDING OFF
Stored Procedure
SaveSalesOrder
1 CREATE PROCEDURE [dbo].[SaveSalesOrder]
2 (
3 @OrderNumber VARCHAR(10),
4 @OrderDate DATETIME,
5 @CustomerNumber VARCHAR(10),
6 @LineItems text
7 )
8 AS
9
10 SET NOCOUNT ON
11
12 BEGIN TRAN
13
14 -- Initialize XML handle
15 DECLARE @hdoc INT
16 EXEC sp_xml_preparedocument @hdoc OUTPUT, @LineItems
17
18 -- Insert data to order header
19 INSERT INTO OrderHeader (OrderNumber, OrderDate, CustomerNumber)
20 VALUES (@OrderNumber, @OrderDate, @CustomerNumber)
21
22 -- Take the IDENTITY of the new record
23 DECLARE @OrderID INT
24 SET @OrderID = SCOPE_IDENTITY()
25
26 -- Insert data to Order Details
27 INSERT INTO OrderDetails( OrderHeaderID, ItemNumber, Qty, Rate)
28 SELECT @OrderID, x.ItemNumber, x.Qty, x.Rate
29 FROM OPENXML ( @hdoc, '/lineItems/item', 2 ) WITH (
30 ItemNumber VARCHAR(20) 'itemNumber',
31 Qty INT 'qty',
32 Rate FLOAT 'rate'
33 ) AS x
34
35 -- Release XML handle
36 EXEC sp_xml_removedocument @hdoc
37
38 IF @@ERROR <> 0
39 ROLLBACK TRAN
40 ELSE
41 COMMIT TRAN
Calling the procedure
1 EXECUTE dbo.SaveSalesOrder '100001', '01-01-2007', 'JAC001',
2 '
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 '
Conclusions
We just saw how to process an XML buffer in TSQL and access the data elements. The next few articles will discuss more complex XML processing and the new XML data type introduced by SQL Server 2005