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>

Read more: C# Corner

Posted via email from .NET Info