ccravenbartle
asked on
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-0 1" 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-0 1" 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-0 1" Reference="102500">
<InvLine Sequence="0" ItemCode="XYZ001" Quantity="1" UnitPrice="100.00" />
</InvHeader>
</Invoice>
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-0
<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-0
<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-0
<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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your help - sorry it took so long to award the points.
This the simplest way to get this xml into table. then you can use this query to insert data into your actual table
Open in new window