Era of New Technology: Insert into Table with XML Value as Parameter

Insert into Table with XML Value as Parameter

Created a Table Called Temp :

CREATE TABLE TEMP (ID Int, Name Varchar(50))

Insert some values into the Table Temp :

INSERT INTO TEMP (ID, Name)
SELECT 1, 'VASANTH' UNION ALL
SELECT 2, 'RAJA' UNION ALL
SELECT 3, 'KARTIK'

To View the Inserted Records in the Table Temp :

SELECT * FROM TEMP

Stored procedure to Insert the Record in the Table Temp with XML Value as Input Parameter

CREATE PROCEDURE usp_InsertTemp
@XMLValue VARCHAR(MAX)
AS
BEGIN

DECLARE @XmlVar INT

EXEC sp_xml_preparedocument @XmlVar OUTPUT, @XMLValue

SELECT ID, Name INTO #TEMP FROM OPENXML(@Xmlvar, '/InsertTemp/Val', 2)
WITH(ID int, Name varchar(50))

EXEC sp_xml_removedocument @XmlVar

INSERT INTO temp (ID, Name) SELECT ID, Name From #TEMP

END

To execute the Stored Procedure :

EXEC usp_InsertTemp
'<InsertTemp>

<Val><ID>4</ID><Name>Singh</Name></Val>

<Val><ID>5</ID><Name>Vinoth</Name></Val>

<Val><ID>6</ID><Name>prakash</Name></Val>

</InsertTemp>'

The XML is framed like

In the Open XML Function the RootNode and ParentNode has to be defined clearly.