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.