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>A001</itemNumber>
4
<qty>10</qty>
5
<rate>10.5</rate>
6
</item>
7
<item>
8
<itemNumber>A002</itemNumber>
9
<qty>20</qty>
10
<rate>11</rate>
11
</item>
12
<item>
13
<itemNumber>A003</itemNumber>
14
<qty>30</qty>
15
<rate>13</rate>
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
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
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 '<lineItems>
3
<item>
4
<itemNumber>A001</itemNumber>
5
<qty>10</qty>
6
<rate>10.5</rate>
7
</item>
8
<item>
9
<itemNumber>A002</itemNumber>
10
<qty>20</qty>
11
<rate>11</rate>
12
</item>
13
<item>
14
<itemNumber>A003</itemNumber>
15
<qty>30</qty>
16
<rate>13</rate>
17
</item>
18 </lineItems>'
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.