How to read data from XML string in SQL Server

In this article we will learn about How to read data from XML string in SQL Server. Please read my previous article Formatting number to add leading zeros in SQL Server.

The goal of this post is to read XML data from an XML file. We will create respective tables accordingly and obtain some sort of meaningful information. It goes over the steps we need to perform in SQL in order to complete this.

Goals

1. Read an XML file and save it in a table. (For a clearer approach, we are creating a separate table for the raw XML)
2. Fetch the XML field data from a raw XML table.
3. Create table(s) according to XML data.

The XML File

The following XML file (Customers.xml) contains the records of Customers. The data is stored as attribute within the tags. This XML will be passed as String to the Stored Procedure.

<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

Stored Procedure for reading, parsing and inserting XML data

The below stored procedure is accepting a parameter of type XML (which would be passed from the code behind). This XML object is parsed and the Attribute and Tag values are fetched and inserted into the Table.

The nodes function of the XML data type is uses XQuery expression to pull out the XML nodes from the XML, for this case I need to fetch the Customer nodes and hence the expression is i.e. /Customers/Customer where Customers is the Root Node and Customer is the child node.

Once the nodes are fetched we need to extract the attribute and tag Inner Text values. For fetching the Inner Text values between the Tags we need to make use of the values function.

The values function can read the Attribute as well as the Inner Text.

CREATE PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
      SET NOCOUNT ON;
 
      INSERT INTO CustomerDetails
      SELECT
      Customer.value('@USER_ID','INT') AS Id, --ATTRIBUTE
      Customer.value('@USER_NAME','VARCHAR(100)') AS UserName, --TAG
      Customer.value('@PASSWORD','VARCHAR(100)') AS Password --TAG
      FROM
      @xml.nodes('/Users/user')AS TEMPTABLE(Customer)
END

Executing the Stored Procedure

The Stored Procedure is executed using the EXEC command as shown below.

EXEC [InsertXML]'<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>'
SELECT * FROM Users

Run the application and see the output

Conclusion

So far this article we learnt about How to read data from XML string in SQL Server.