In this article I will explain with an example, how to read, parse and insert XML File data to SQL Server Table using Stored Procedure.
SQL Server 2005 and above versions, allow to read and parse and XML file using the XML data type and it also provide functions to parse the XML and extract its Attribute and Tag values.
I have created a simple table named CustomerDetails whose schema is shown below.
Note: The SQL for creating the database is provided in the attached sample code.
The XML File
The following XML file (Customers.xml) contains the records of Customers. The data is stored as attribute within the tags. This XML will be passed as String to the Stored Procedure.
Note: The following XML file will be saved in a folder on DISK and the path of this file will be passed to the Stored Procedure.
<?xml version="1.0" standalone="yes"?>
<Customer Id ="1">
<Customer Id = "2">
<Customer Id ="3">
<Customer Id ="4">
Stored Procedure for reading, parsing and inserting XML File data
The below stored procedure is accepting the path of the XML file as parameter.
Using the path of the XML file, first the XML file is read using the OPENROWSET function into a variable of XML data type.
Then this XML variable is parsed and the Attribute and Tag values are fetched and inserted into the Table.
The nodes function of the XML data type is uses XQuery expression to pull out the XML nodes from the XML, for this case I need to fetch the Customer nodes and hence the expression is i.e. /Customers/Customer where Customers is the Root Node and Customer is the child node.
Once the nodes are fetched we need to extract the attribute and tag Inner Text values. For fetching the Inner Text values between the Tags we need to make use of the values function.
The values function can read the Attribute as well as the Inner Text.
In order to read the attribute we need to pass the name of the Attribute prefix with @ and its data type, in this example the attribute Id is fetched using Customer.value('@Id', 'INT').
In order to fetch the inner text we need to pass the name of the Tag and its data type. The Inner Text of the XML tag is fetched using text function and we also make use of an index  which means it should fetch only the first matched value.
Finally the values are inserted into the CustomerDetails table.
CREATE PROCEDURE [dbo].[InsertXMLFile]
SET NOCOUNT ON;
DECLARE @xml XML
DECLARE @SQL NVARCHAR(1000)= 'SET @xml = (SELECT * FROM OPENROWSET (BULK ''' + @FilePath + ''', SINGLE_CLOB) AS XmlData)'
EXEC sp_executesql @SQL, N'@xml XML OUTPUT', @xml OUTPUT;
INSERT INTO CustomerDetails
Customer.value('@Id','INT') AS Id, --ATTRIBUTE
Customer.value('(Name/text())','VARCHAR(100)') AS Name, --TAG
Customer.value('(Country/text())','VARCHAR(100)') AS Country --TAG
Executing the Stored Procedure
The Stored Procedure is executed using the EXEC command as shown below.
EXEC [InsertXMLFile] 'D:\Files\CustomerData.xml'
SELECT * FROM CustomerDetails