How to read data from XML string

--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="Cherukuri" PASSWORD="IDontKnow" />

                            <USER USER_ID="2" USER_NAME="Venkateswarlu" PASSWORD="Hero" />

                               <USER USER_ID="3" USER_NAME="Legend" PASSWORD="King" />

                   </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

--Case 2

    SET @XMLData=   '<USERS>

<USER>

      <USER_ID>1</USER_ID>

      <USER_NAME>Cherukuri</USER_NAME>

      <PASSWORD>IDontKnow</PASSWORD>

</USER>

<USER>

      <USER_ID>2</USER_ID>

      <USER_NAME>Venkateswarlu</USER_NAME>

      <PASSWORD>Hero</PASSWORD>

</USER>

<USER>

      <USER_ID>3</USER_ID>

      <USER_NAME>Legend</USER_NAME>

      <PASSWORD>King</PASSWORD>

</USER>

</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',2)

WITH #TEMP_TABLE

EXECUTE sp_xml_removedocument @idoc

--Displaying data from Temp Table

SELECT * FROM #TEMP_TABLE

--Removing created Temp Table

DROP TABLE #TEMP_TABLE

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.