Problem
In this article we look at how to load an XML file into a SQL Server table and then how to query the XML data with several query examples.
Solution
There are different ways to achieve this task of importing data from an XML file into a SQL Server table, but I am going to demonstrate one of easiest ways to accomplish this task.
These are the steps I performed for importing data into SQL Server and then parsing the XML into a relational format.
- Import XML data from an XML file into SQL Server table using the OPENROWSET function
- Parse the XML data using the OPENXML function
Importing XML data from XML file using OPENROWSET
I have an XML file downloaded from my FTP location to a local folder and data in this XML file looks like below. You can download the sample data here.
Now in order to import data from the XML file to a table in SQL Server, I am using the OPENROWSET function as you can see below.
In the script below, I am first creating a table with a column of data type XML and then reading the XML data from the file using the OPENROWSET function by specifying the file location and name of the XML file as you can see below:
CREATE DATABASE OPENXMLTesting
GO
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
SELECT * FROM XMLwithOpenXML
When I query the table in which I have imported the XML data, it looks like this. The XMLData column is an XML data type, it will output a hyperlink as shown below:
Clicking on the hyperlink, in the above image, will open another tab within SSMS with the XML data displayed as shown below.
Process XML data using OPENXML function
Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.
We will first call the sp_xml_preparedocument stored procedure by specifying the XML data which will then output the handle of the XML data that it has prepared and stored in internal cache.
Then we will use the handle returned by the sp_xml_preparedocument stored procedure in the OPENXML function to open the XML data and read it.
Note: the sp_xml_preparedocument stored procedure stores the XML data in SQL Server’s internal cache, it is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. We should call the sp_xml_removedocument stored procedure as early possible, so that internal cache can be freed for other usage.
USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)
EXEC sp_xml_removedocument @hDoc
GO
From the above XML data, I want to retrieve all the customer information, so I am navigating to the Customer element and querying CustomerID and CustomerName (please note the use of “@” before the name of the attribute) attributes and Address element in the above SELECT statement using the OPENXML function.
The structure of the resultset can be determined with the “WITH” clause as shown above.
From the above XML data, I now want to retrieve all the customer information along with OrderID and OrderDate placed by each individual customer and hence I am navigating to the Order element and then querying OrderID and OrderDate attributes.
If we want to navigate back to the parent or grand parent level and get data from there, we need to use “../” to read the parent’s data and “../../” to read the grand parent’s data and so on.
USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order')
WITH
(
CustomerID [varchar](50) '../../@CustomerID',
CustomerName [varchar](100) '../../@CustomerName',
Address [varchar](100) '../../Address',
OrderID [varchar](1000) '@OrderID',
OrderDate datetime '@OrderDate'
)
EXEC sp_xml_removedocument @hDoc
GO
The result of the above query can be seen in the image below. You can see below all the customers and all the orders placed by each customer.
Now let’s go one level deeper. This time from the above XML data, I want to retrieve all the customer information and their orders along with ProductID and Quantity from each order placed. And hence, as you can see below I am navigating to the OrderDetail and retrieving the ProductID and Quantity attributes’ values. At the same time I am using “../” to reach the parent level to get Order information available at the parent level whereas I am using “../../../” to reach to the great grand parent level to grab Customer information as shown below:
USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH
(
CustomerID [varchar](50) '../../../@CustomerID',
CustomerName [varchar](100) '../../../@CustomerName',
Address [varchar](100) '../../../Address',
OrderID [varchar](1000) '../@OrderID',
OrderDate datetime '../@OrderDate',
ProductID [varchar](50) '@ProductID',
Quantity int '@Quantity'
)
EXEC sp_xml_removedocument @hDoc
GO
The result of the above query can be seen in the image below. You can see all the customer information and their orders along with ProductID and Quantity from each order placed.
Next Steps
- Review these related tips
