Recent Posts

Thursday, March 13, 2008

Procedure to insert XML Data in Sql Server Database using OPEN XML

Xml file is:


"The Procedure is

CREATE PROCEDURE xmlOrderInsert @order ntext AS
DECLARE @docHandle int, @OID int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @order

--sp_xml_preparedocument makes xml document ready to read
-- @Order holds the xml data eg . set @order='....'


BEGIN TRANSACTION

INSERT INTO Orders( CustomerID, EmployeeID, OrderDate, RequiredDate )
SELECT CustomerID, EmployeeID, OrderDate, RequiredDate
FROM Openxml( @docHandle, '/Order', 3) WITH ( CustomerID nchar(5),
EmployeeID int, OrderDate datetime, RequiredDate datetime )

IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -100 END
SET @OID = SCOPE_IDENTITY()
INSERT INTO [Order Details] ( OrderID, ProductID, UnitPrice, Quantity, Discount )
SELECT @OID AS [PO ID], ProductID, UnitPrice, Quantity, Discount
FROM OpenXml( @docHandle, '/Order/OrderDetails', 1) WITH
( ProductID int, UnitPrice money, Quantity smallint, Discount real )
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
EXEC sp_xml_removedocument @docHandle SELECT @OID AS [Order ID]
GO


' to execute the procedure

exec xmlOrderInsert
@Order='.. '

' this should insert the correct data
'Thanks

Related Posts by Categories




4 comments:

sneha said...

hi,
Thanks for you article.What should i do if i want to create table from xml dynamically first and then insert data to that table ?

rprateek said...

just write create table statement first in the stored procedure, create table and then write the insert into statement. This should work for you.

Prabu Nivas said...

Hi
I just run through your blog its really cool, I am new to VB. I developing small application in VB. Retrieving Excel data and i need to convert those data into XML file. Its possible from VB, I am awaiting for your response.

Thanks and Regards
S.Prabu

rprateek said...

Go through by blog article Read Excel file from VB 6.0. Then after reading it use the data to convert it into xml tags and save it in the text file with the extension .xml. This should work for you.

Post a Comment