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

In this article, I’ll describe how to retrieve XML data from an XML file using a SQL stored procedure. Visit this page to learn more about SQL Server.

Stored Procedure for reading XML File

• Above XMLFile.xml which contain the XML file data.

<DataSet>
  <tblEmployee>
    <name>Yankit</name>
    <Address>Surat</Address>l
    <designation>Developer</designation>l
  </tblEmployee>
  <tblEmployee>
    <name>Justin</name>
    <Address>Ahemdabad</Address>l
    <designation>System Analyst</designation>l
  </tblEmployee>
</DataSet>

• The aforementioned stored function USP READXMLFILE reads the XML file from the designated location and saves it in a variable called @XMLDOC.
• The built-in function SP XML PREPAREDOCUMENT is then used to parse the XML data in order to produce a handle.
• Then, using a supplied path and the OPENXML function, data is extracted from the XML document and stored in a table-like structure with the columns “name” , “Address” , “designation” specified.
• This makes it simple to query and process the data within the stored method.
• The xml file will be read from the location d:TestXML.xml when you run this stored procedure.
• If you want to read from a different location, the path should be modified accordingly.
• It’s also critical to note that this stored procedure is primarily intended to read a specific type of XML, and that your XML file structure must be taken into account while updating the path and column structure.

Stored Procedure for reading data from XML String

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

• The XML string is read by the aforementioned stored method USP READXMLString and saved in the variable @XMLDOC2.
• The XML data is then parsed using the built-in function SP XML PREPAREDOCUMENT to provide a handle.
• The data is then extracted from the XML document using the OPENXML function and stored in a table-like structure with the columns “name” ,”Address” “designation” given.
• This makes it simple to query and process the data within the stored method.