How to Read XML file and XML String through SQL Stored Procedure

Introduction

In this article, I will explain reading XML data from XML String \ XML File through SQL Stored Procedure. Find more about SQL Server here: SQL Server.

Stored Procedure for reading XML File

There is an XML file, TestXML.xml, which contains the following XML data.

<DataSet>
  <tblEmp>
    <name>Vishal</name>
    <designation>Developer</designation>l
  </tblEmp>
  <tblEmp>
    <name>Jibin</name>
    <designation>System Analyst</designation>l
  </tblEmp>
</DataSet>

SQL Stored Procedure to read XML File

CREATE PROC [dbo].[USP_READXMLFILE]   
AS     
 /*     
  EXEC [USP_READXMLFILE]
 */     
  BEGIN     
        SET NOCOUNT ON                     
        DECLARE @HANDLE INT             
        DECLARE @SQUERY VARCHAR(1000)
        DECLARE @XMLDOC XML 
       
        SET @XMLDOC = (SELECT * FROM OPENROWSET
                      (BULK 'd:\TestXML.xml', SINGLE_CLOB) AS XMLDATA)
                       SELECT @XMLDOC                         

        EXEC SP_XML_PREPAREDOCUMENT  @HANDLE OUTPUT,@XMLDOC     
        SELECT * FROM OPENXML(@HANDLE, '/XMLData/tblEmp', 2)      
        WITH (name VARCHAR(50),designation VARCHAR(50))
  END 

The above stored procedure USP_READXMLFILE reads an XML file from a specified location and stores it in a variable named @XMLDOC. Then it uses the built-in function SP_XML_PREPAREDOCUMENT to parse the XML data and create a handle.

The OPENXML function is then used to extract data from the XML document using a specified path and store it in a table-like structure with columns specified as 'name' and 'designation'. This allows the data to be easily queried and processed within the stored procedure.

It's important to mention that when you run this stored procedure it will read the xml file from the location d:\TestXML.xml, if you want to read from a different location, the path should be updated accordingly. It's also important to mention that this stored procedure is mainly designed to read a specific format of XML, and you need to update the path and column structure based on your XML file structure.

Stored Procedure for reading data from XML String

XML String

<DataSet>
  <tblEmp>
    <name>Vishal</name>
    <designation>Developer</designation>l
  </tblEmp>
  <tblEmp>
    <name>Jibin</name>
    <designation>System Analyst</designation>l
  </tblEmp>
</DataSet>

Stored Procedure to read XML string 

CREATE PROC [dbo].[USP_READXMLString]   
(     
   @XMLDOC2    XML     
)     
AS     
   BEGIN     
         SET NOCOUNT ON     
        
         DECLARE @HANDLE INT             
         EXEC SP_XML_PREPAREDOCUMENT  @HANDLE OUTPUT,@XMLDOC2     
         SELECT * FROM OPENXML(@HANDLE, '/DataSet/tblEmp', 2)      
         WITH (name VARCHAR(50),designation VARCHAR(50))
END  

The above stored procedure USP_READXMLString reads an XML string passed as a parameter and stores it in the variable @XMLDOC2. Then it uses the built-in function SP_XML_PREPAREDOCUMENT to parse the XML data and create a handle.

The OPENXML function is then used to extract data from the XML document using a specified XPath and store it in a table-like structure with columns specified as 'name' and 'designation'. This allows the data to be easily queried and processed within the stored procedure.

This stored procedure is designed to read a specific format of XML, and you need to update the XPath and column structure based on your XML string format.
It's also important to note that this stored procedure expects an XML data type as a parameter, so while calling this stored procedure, you need to pass the XML as a string and convert it to XML data type.

Conclusion

This article taught us about reading XML data from XML String \ XML File through SQL Stored Procedure. Find more about SQL Server here: SQL Server.