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

Objective

In this article I will explain reading XML data from XML String \ XML File through SQL Stored Procedure.

Stored Procedure for reading XML File.

There is a XML file TestXML.xml which contains 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 
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  
Thanks for reading …