In this Article, i will explain you , how to insert the below XML string into SQL Server Database table in Two Ways :

First Method:

Suppose we have a User XML like below:

<USERS>

   <USER USER_ID="1" USER_NAME="Jayant" PASSWORD="Jaynttrip" />

   <USER USER_ID="2" USER_NAME="Ram" PASSWORD="HeyRam" />

   <USER USER_ID="3" USER_NAME="Jhon" PASSWORD="thomasRT" />

</USERS>

The SQL Query look alike for execute the XML is

--Variables Decleration

DECLARE @XMLData VARCHAR(MAX)

DECLARE @idoc INT


-- Creating Temporary Table

CREATE TABLE #TEMP_TABLE

(

      REC_ID INT IDENTITY(1,1),

      [USER_ID] INT,

      [USER_NAME] VARCHAR(50),

      [PASSWORD] VARCHAR(50),

)

--Case 1

    SET @XMLData=   '<USERS>

                            <USER USER_ID="1" USER_NAME="Jayant" PASSWORD="Jaynttrip" />
                            <USER USER_ID="2" USER_NAME="Ram" PASSWORD="HeyRam" />
                            <USER USER_ID="3" USER_NAME="Jhon" PASSWORD="thomasRT" />

                   </USERS>'

--Reading Data from XML and inserting into Temp Table

EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XMLData

INSERT INTO #TEMP_TABLE

SELECT * FROM OpenXML(@idoc, '/USERS/USER',1)

WITH #TEMP_TABLE

EXECUTE sp_xml_removedocument @idoc

--Displaying data from Temp Table


SELECT * FROM #TEMP_TABLE

DELETE FROM #TEMP_TABLE

Execute the above statement and the Final Output should be like this




Note : XML attribute names and SQL tables name should match. These are case sensitive. If don't match the column in Table with XML attribute name Null will stored in that column.