How to insert XML data into a table in SQL Server.Below is the Stored Procedure for that.....
CREATE PROCEDURE [dbo].[pr_wip_InsertXML]
@dataXML VARCHAR(3000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DocHandle INT
DECLARE @BookName VARCHAR(50)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @dataXML
INSERT INTO dbo.Books(BookName)
SELECT X.* FROM
OPENXML (@DocHandle, '/Books/Book',1)
WITH
(
BookName VARCHAR(50)
) AS x
EXEC sp_xml_removedocument @DocHandle
END
--Below is the XML format
<xml version="1.0" encoding="utf-8" ?>
<Books>
<Book BookName="Let Us C" />
<Book BookName="Asp.Net" />
</Books>
1 comment:
Hi I am Saurav Banerjee,
Nice to read and know
Post a Comment