Solved

Processing XML within Sql Server Stored Procedure

Posted on 2009-04-09
4
445 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:ccravenbartle
  • 2
4 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 125 total points
Comment Utility
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
 
LVL 2

Expert Comment

by:TejasShahMscIT
Comment Utility
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
 
LVL 2

Accepted Solution

by:
TejasShahMscIT earned 375 total points
Comment Utility
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
 

Author Closing Comment

by:ccravenbartle
Comment Utility
Thank you both for your help - sorry it took so long to award the points.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now