How to Extract Data From XML File Using SQL Query | 2 Methods Explained

If you’ve ever dealt with XML files and needed to extract specific data, you’ll appreciate the power and simplicity of using SQL queries for this task. To extract data from an XML file using an SQL query, you can use the SQL Server’s built-in XML functions. 

Or, you can leverage the native XML support in SQL Server. In this article, we’ll elaborate both of these methods and help you learn how to extract data from XML files using SQL queries. Here on we expand on it.

How to Extract Value From XML in SQL?

Below are the two methods mentioned before for extracting data from XML files using SQL queries. 

Method 1: Importing XML Data using OPENROWSET

Below is an example using the OPENROWSET function to read XML data from a file and the nodes() method to shred XML into rows:

-- Enable the OPENROWSET functionality
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'ad hoc distributed queries', 1;
RECONFIGURE;
-- Example query to extract data from an XML file
DECLARE @xmlData XML;
-- Replace 'C:\Path\To\Your\File.xml' with the actual path to your XML file
SELECT @xmlData = BulkColumn
FROM OPENROWSET(BULK 'C:\Path\To\Your\File.xml', SINGLE_BLOB) AS x;
-- Use the nodes() method to shred XML into rows
SELECT
    xmlData.value('(//ElementName1)[1]', 'nvarchar(50)') AS Column1,
    xmlData.value('(//ElementName2)[1]', 'nvarchar(50)') AS Column2,
    -- Add more columns as needed
FROM @xmlData.nodes('//RootNode') AS T(xmlData);

Replace ‘C:\Path\To\Your\File.xml’ with the actual path to your XML file. And adjust the XPath expressions within the value() method to point to the specific elements you want to extract from your XML file. Also, you may have to modify the data types and XPath expressions based on the structure of your XML file. 

Keep in mind that the ability to use OPENROWSET and other related functionalities might be restricted based on your SQL Server configuration and security settings. Ensure that you have the necessary permissions and configurations to execute these queries.

Method 2: Using Native XML Support in SQL Server

If you’re using SQL Server, it has native support for XML data types. You can store XML data in a column with the XML data type, making it easier to work with XML content using SQL queries. 

CREATE TABLE XmlData (
    Id INT PRIMARY KEY,
    XmlContent XML
);
INSERT INTO XmlData (Id, XmlContent)
VALUES (1, '<data><name>John</name><age>30</age></data>');

With the basics out of the way, here are a few techniques for optimizing queries when working with XML data stored in SQL Server tables.

1. Extracting Element Values

To extract the values of specific elements from an XML file, use the .value() method in combination with the XQuery language.

SELECT
    XmlContent.value('(data/name)[1]', 'VARCHAR(50)') AS Name,
    XmlContent.value('(data/age)[1]', 'INT') AS Age
FROM XmlData
WHERE Id = 1;

2. Extracting Attribute Values

To extract attribute values, use the @ symbol followed by the attribute name.

SELECT
    XmlContent.value('(data/person/@id)[1]', 'INT') AS PersonId,
    XmlContent.value('(data/person/@role)[1]', 'VARCHAR(50)') AS ROLE
FROM XmlData
WHERE Id = 1;

3. Extracting Nested Data

XML files often have nested structures. Use the appropriate XQuery expressions to navigate through the hierarchy.

SELECT
    XmlContent.value('(data/address/city)[1]', 'VARCHAR(50)') AS City,
    XmlContent.value('(data/address/state)[1]', 'VARCHAR(50)') AS State
FROM XmlData
WHERE Id = 1;

4. Handling Multiple Rows

If your XML data is stored in multiple rows, adjust the queries to retrieve information from the entire dataset.

SELECT
    Id,
    XmlContent.value('(data/name)[1]', 'VARCHAR(50)') AS Name,
    XmlContent.value('(data/age)[1]', 'INT') AS Age
FROM XmlData;

Frequently Asked Questions

How to extract XML data from tables in SQL Server?

XQuery within SQL Server facilitates the querying and extraction of data from XML documents. It provides various methods to retrieve information from XML documents, allowing for the application of data filters or WHERE clauses on XML elements.

How to get XML node value in SQL Server?

You can extract multiple values from the row set using the value() method applied to the nodes() result. However, when directly applied to the XML instance, the value() method returns only a single value.

How do I handle XML namespaces when extracting XML data?

If your XML file uses namespaces, you need to declare them in the query using the WITH XMLNAMESPACES clause.

Conclusion

In conclusion, using SQL queries for XML data extraction streamlines the process in a SQL-native manner within a relational database. This simplifies integration with relational database queries. We hope this guide clarified the XML data extraction process using SQL queries. If you have any questions or feedback, feel free to reach out. Happy querying!