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.
