Wednesday, May 18, 2011

How To Insert XML file in DataBase Using OpenXML


DECLARE @idoc int
DECLARE @doc varchar(1000)

--Create a Temporary Table
Create Table #TempTable([Name] VARCHAR (100),Age VARCHAR (100), Address VARCHAR (100),Hobby VARCHAR (100),BANKCODE VARCHAR (100),BANKACCT VARCHAR (100))

--Assignment of Xml data in a string/xml dataType E.G DECLARE @doc varchar(1000)/DECLARE @doc xml
SET @doc ='Please Refer to Attached Image For XMl Data'

-- Create internal DOM representation of the XML document.
--This Will return a XMlDocument Object which can be iterated with the help of X-Path Query
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--Inserting Data into Temporary Table #TempTable
Insert into #TempTable
SELECT * FROM
--Reading @idoc with OPENXML
OPENXML (@idoc, 'Verification/PersonalDetail', 2)
WITH ([Name] VARCHAR (100),Age VARCHAR (100), Address VARCHAR (100),Hobby VARCHAR (100),BANKCODE VARCHAR (100),BANKACCT VARCHAR (100))

--Removing Compiled Xml Document Object from memory
EXEC sp_xml_removedocument @idoc

SELECT * FROM #TempTable
DROP TABLE #TempTable

No comments: