XML (eXtensible Markup Language) is a popular format for storing and exchanging data. In SQL Server, you can import XML data into a table and query it using various methods. In this article, we will explore one of the easiest ways to accomplish this task.

Importing XML Data from an XML File

To import XML data from an XML file into a SQL Server table, you can use the OPENROWSET function. Here are the steps:

1. Create a table with a column of data type XML.
2. Read the XML data from the file using the OPENROWSET function.
3. Insert the XML data into the table.

Here is an example:

CREATE TABLE XMLData (
  Id INT IDENTITY PRIMARY KEY,
  XMLContent XML,
  LoadedDateTime DATETIME
)

INSERT INTO XMLData (XMLContent, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Path\To\XMLFile.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLData;

After importing the XML data, you can query the table and retrieve the XML content.

Querying XML Data

Once the XML data is imported into a table, you can use SQL Server’s XML functions or the sp_xml_preparedocument stored procedure along with the OPENXML function to process the XML data.

Here is an example of using the OPENXML function:

DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = XMLContent FROM XMLData

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;

In the above example, we retrieve customer information from the XML data by navigating to the Customer element and querying the CustomerID, CustomerName, and Address attributes.

You can also navigate to nested elements and retrieve data from them. For example:

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'
)

In the above example, we retrieve customer information along with the OrderID and OrderDate attributes from each order placed by the customer.

You can continue navigating to deeper levels and retrieve more data as needed.

Conclusion

In this article, we have explored how to import XML data into a SQL Server table and query it using the OPENROWSET function and the OPENXML function. XML data can be processed and retrieved using various SQL Server functions and stored procedures. By understanding these concepts, you can effectively work with XML data in your SQL Server databases.

Article Last Updated: 2022-02-24