Processing XML within Sql Server Stored Procedure

Using a SQL Server Stored Procedure, I am taking my first tentative steps at inserting data from an xml file that has been passed to the procedure as an input parameter. Using the ProcessXMLInvoice procedure below, I can process an xml file, consisting of a single invoice transaction, to insert the invoice header and then insert the invoice lines.  

However, I need to modify the Stored Procedure to be able to process xml files containing multiple invoice transactions but I cannot work out how to modify the procedure to loop around so that, in turn, it inserts the invoice header, uses the identity of the header to set the key for the invoice lines, inserts the invoice lines and then moves on to the next invoice.

Below are sample xml files for single and multiple invoice transactions

Single Transaction XML
<Invoice>
  <InvHeader AccountID="00002451" TransactionDate="2009-01-01" Reference="102480">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
      <InvLine Sequence="1" ItemCode="XYZ002" Quantity="3" UnitPrice="110.00" />
  </InvHeader>
</Invoice>

Multiple Transaction XML
<Invoice>
  <InvHeader AccountID="00002451" TransactionDate="2009-01-01" Reference="102480">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
      <InvLine Sequence="1" ItemCode="XYZ002" Quantity="3" UnitPrice="110.00" />
  </InvHeader>
  <InvHeader AccountID="00002490" TransactionDate="2009-01-01" Reference="102500">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
  </InvHeader>
</Invoice>



CREATE PROCEDURE [dbo].[ProcessXMLInvoice]
@xml xml
 
AS
-------------------------------------------------------------------------
DECLARE @xml_handle int
DECLARE @TransactionId int
 
BEGIN TRANSACTION
 
EXEC sp_xml_preparedocument @xml_handle OUTPUT, @xml
 
INSERT INTO InvoiceHeader (AccountID,  TransactionDate, Reference)
SELECT AccountID,  TransactionDate, Reference)
FROM OPENXML (@xml_handle, '/Invoice/InvHeader',1)
      WITH (AccountID  varchar(10), TransactionDate datetime, Reference varchar(50))
IF @@ERROR <> 0 GOTO ERROR_HANDLER
 
SET @TransactionID = (SELECT @@IDENTITY)
 
INSERT INTO InvoiceLines
(TransactionID, Sequence, ItemCode, Quantity, UnitPrice)
SELECT @TransactionID, Sequence, ItemCode, Quantity, UnitPrice 
FROM OPENXML (@xml_handle, '/Invoice/InvHeader/InvLine',1)
      WITH (Sequence int, ItemCode varchar(50), Quantity int, UnitPrice dec(10,2))
IF @@ERROR <> 0 GOTO ERROR_HANDLER
 
EXEC sp_xml_removedocument @xml_handle
 
COMMIT TRANSACTION
RETURN @TransactionID
ERROR_HANDLER:
ROLLBACK TRANSACTION
RETURN -9

Open in new window

ccravenbartleAsked:
Who is Participating?
 
TejasShahMscITConnect With a Mentor Commented:
Hi,

We have the same issue last year.

To come over this issue, we did following:

We save SEQUENCE in Header Table, so we can make join on it to insert data into details table for particular row.

Header table has column HeaderID should be inserted to Details Table.

I used one table variable which contains HeaderID and Sequence, which we used to insert data in Details table.

Let me know if it helps you in any way.

Please find a query below:
DECLARE @xml XML
SELECT @xml = '<Invoice>
  <InvHeader AccountID="00002451" TransactionDate="2009-01-01" Reference="102480">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
      <InvLine Sequence="1" ItemCode="XYZ002" Quantity="3" UnitPrice="110.00" />
  </InvHeader>
  <InvHeader AccountID="00002490" TransactionDate="2009-01-01" Reference="102500">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
  </InvHeader>
</Invoice>'
 
DECLARE @TestTable TABLE(Identitycolumn INT, Account VARCHAR(10))
 
INSERT INTO  Header(
			column1,
			column2,
			..
			..
			..
			) 
		inserted.HeaderID, inserted.Account INTO @TestTable
SELECT 
        x.v.value('../@AccountID','VARCHAR(100)') AS AccountID,
        x.v.value('../@TransactionDate','DATETIME') AS TransactionDate,
        x.v.value('../@Reference','VARCHAR(100)') AS TransactionDate,
        x.v.value('@Sequence','INT') AS Sequence,
        x.v.value('@ItemCode','VARCHAR(10)') AS ItemCode,
        x.v.value('@Quantity','INT') AS Quantity,
        x.v.value('@UnitPrice','NUMERIC(18,3)') AS UnitPrice
FROM @xml.nodes('Invoice/InvHeader/InvLine') x(v)
 
INSERT INTO Details(
		HeaderID,
		..,
		..,
		..
	)
SELECT t.HeaderID 
FROM (
SELECT 
        x.v.value('../@AccountID','VARCHAR(100)') AS AccountID,
        x.v.value('../@TransactionDate','DATETIME') AS TransactionDate,
        x.v.value('../@Reference','VARCHAR(100)') AS TransactionDate,
        x.v.value('@Sequence','INT') AS Sequence,
        x.v.value('@ItemCode','VARCHAR(10)') AS ItemCode,
        x.v.value('@Quantity','INT') AS Quantity,
        x.v.value('@UnitPrice','NUMERIC(18,3)') AS UnitPrice
FROM @xml.nodes('Invoice/InvHeader/InvLine') x(v)
) x 
INNER JOIN @TestTable t ON t.AccoutID = x.AccountID
		AND t.Sequence = x.Sequence

Open in new window

0
 
chapmandewConnect With a Mentor Commented:
First, you want to get rid of this line:
SET @TransactionID = (SELECT @@IDENTITY)

You want to replace this functionality with the new output clause in 2005:
http://articles.techrepublic.com.com/5100-10878_11-6074046.html

This will allow you to enter however many records are in the Header, capture them (the identity values) into a temp table, then use that temp table to insert your new identity values into your InvoiceLines table.
0
 
TejasShahMscITCommented:
Hi,

This the simplest way to get this xml into table. then you can use this query to insert data into your actual table
DECLARE @xml XML
SELECT @xml = '<Invoice>
  <InvHeader AccountID="00002451" TransactionDate="2009-01-01" Reference="102480">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
      <InvLine Sequence="1" ItemCode="XYZ002" Quantity="3" UnitPrice="110.00" />
  </InvHeader>
  <InvHeader AccountID="00002490" TransactionDate="2009-01-01" Reference="102500">
      <InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
  </InvHeader>
</Invoice>'
 
 
SELECT 
	x.v.value('../@AccountID','VARCHAR(100)') AS AccountID,
	x.v.value('../@TransactionDate','DATETIME') AS TransactionDate,
	x.v.value('../@Reference','VARCHAR(100)') AS TransactionDate,
	x.v.value('@Sequence','INT') AS Sequence,
	x.v.value('@ItemCode','VARCHAR(10)') AS ItemCode,
	x.v.value('@Quantity','INT') AS Quantity,
	x.v.value('@UnitPrice','NUMERIC(18,3)') AS UnitPrice
FROM @xml.nodes('Invoice/InvHeader/InvLine') x(v)

Open in new window

0
 
ccravenbartleAuthor Commented:
Thank you both for your help - sorry it took so long to award the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.