Problem
XML is a complex data type used for representing objects with various properties. In the XML format, you use elements or attributes to describe the content of your payload. XML files can be relatively flat or have multiple levels of nesting. What are some common ways to work with XML in SQL Server?
Solution
The goal of this document is to provide an overview of working with XML in SQL Server 2025 using T-SQL. We will go over the following scenarios: get any dataset as XML, query native XML, and finally convert XML to JSON. Comparing XML to JSON with their inherent advantages and disadvantages is out of the scope of this article. Both data types have a place in the data domain depending on the use case.
Solution Setup
To follow this document hands-on, you need to:
- Install SQL Server 2025. Follow this link to register for a free trial.
- Next, import the Adventure Works 2022 sample database to your newly installed SQL Server instance. To get the database backup file follow this link. For a step by step guide how to import the backup check this article.
Having some data let us examine how we can work with XML in SQL Server 2025 using T-SQL.
FOR XML AUTO
First, let us say we want to get some sales data. Additionally, we want to calculate sales quantity by dividing each Line Total by the Unit Price:
--MSSQLTips.com T-SQL
SELECT Cust.CustomerID
, OrderHeader.CustomerID
, OrderHeader.SalesOrderID
, OrderHeader.STATUS
, SalesDetails.ProductID
, SalesDetails.UnitPrice
, SalesDetails.LineTotal
, SalesDetails.LineTotal / SalesDetails.UnitPrice Qty
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
ORDER BY Cust.CustomerID
FOR XML AUTO ;To do a “sanity check” of the result set, let us run the query up to and including the ORDER BY clause:
Looks like exactly what we would expect – for each customer we get the Sales Order ID and related details. Now, let us run the full statement including the FOR XML AUTO clause. AUTO mode returns query results as nested XML elements:
What the expanded XML looks like:
We can observe the following:
- We get a root xml element for Customer with attribute CustomerID corresponding to the first table we started joining to (Sales.Customer)
- We get nested elements for each OrderHeader pertaining to that customer, corresponding to the first joined table (Sales.SalesOrderHeader)
- We get nested elements for each Product pertaining to that sales order, corresponding to the second joined table (Sales.SalesOrderDetail)
Looks like FOR XML AUTO is the easiest way to get your result set as XML. Still, keep in mind the following:
- For each table in the FROM clause, from which at least one column is selected, you will get an XML element.
- The XML hierarchy (nesting of the elements) in the resulting XML is based on the order of tables identified by the columns specified in the SELECT clause. Therefore, the order in which column names are specified in the SELECT clause is significant.
- If the optional
ELEMENTSoption is specified in theFOR XMLclause, the columns listed in theSELECTclause will be mapped to attributes or subelements.
FOR XML AUTO ELEMENTS
Next, we can fine-tune the query by adding the ELEMENTS keyword to the FOR XML clause.
SELECT Cust.CustomerID Customer
, OrderHeader.CustomerID
, OrderHeader.SalesOrderID
, OrderHeader.STATUS
, SalesDetails.ProductID
, SalesDetails.UnitPrice
, SalesDetails.LineTotal
, SalesDetails.LineTotal / SalesDetails.UnitPrice Qty
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS;
This change will automatically generate an element-centric XML instead of the previously generated elements with attributes XML. Therefore, it is handy to use ELEMENTS when you want each column in the SELECT statement to become an element:
FOR XML PATH
Another approach to extract the result set in an XML format is to add the PATH keyword to the FOR XML clause. The PATH mode provides a simpler way to mix elements and attributes. We can fine-tune what elements and attributes we get by changing the column alias:
- If the column alias starts with a “@” an attribute in the row element will be created
- If the column alias doesn’t start with a “@”, isn’t one of the XPath node tests, and doesn’t contain a slash mark (/), an XML element that is a subelement of the row element, row by default, is created.
- If the column name doesn’t start with an “@”, but contains a slash mark (/), the column name indicates an XML hierarchy.
SELECT Cust.CustomerID "@Customer"
, OrderHeader.CustomerID "OrderHeaderCustomerID"
, OrderHeader.SalesOrderID "OrderHeaderSalesOrderID"
, OrderHeader.STATUS "OrderHeaderStatus"
, SalesDetails.ProductID "SalesDetails/ProductID"
, SalesDetails.UnitPrice "SalesDetails/UnitPrice"
, SalesDetails.LineTotal "SalesDetails/LineTotal"
, SalesDetails.LineTotal / SalesDetails.UnitPrice "SalesDetails/Qty"
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
ORDER BY Cust.CustomerID
FOR XML PATH;
The important differences between using PATH and AUTO with or without ELEMENTS here are:
- We get a row element for each Customer with an attribute “Customer” as specified in the query
- OrderHeader details are elements
- SalesDetails is another element with nested elements for the details such as the ProductID, UnitPrice, etc.:
Now let us tweak this result set by removing the column OrderHeader.CustomerID, making the OrderHeader details an attribute, and turning Sales Details to nested elements:
While we still get a row for each Customer and Order, this modified result set is more compact and focuses on the SalesDetails, while the OrderHeader details became attributes only:
FOR XML EXPLICIT
Finally, when it comes to representing any table data as XML, we can use the FOR XML EXPLICIT clause. This mode will allow us to get full control of how we build our XML and precisely specify the hierarchy of the selected columns.
-- First level: distinct Customer elements for each unique Customer
SELECT DISTINCT 1 Tag
, NULL Parent
, Cust.CustomerID [Customer!1!CustomerID]
, NULL [Order!2!SalesOrderID!ELEMENT]
, NULL [Order!2!Status!ELEMENT]
, NULL [OrderDetail!3!ProductID!ELEMENT]
, NULL [OrderDetail!3!UnitPrice!ELEMENT]
, NULL [OrderDetail!3!LineTotal!ELEMENT]
, NULL [OrderDetail!3!Qty!ELEMENT]
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
UNION ALL
-- Second level: Order elements - only distinct orders per customer
SELECT 2 Tag
, 1 Parent
, Cust.CustomerID
, OrderHeader.SalesOrderID
, OrderHeader.STATUS
, NULL
, NULL
, NULL
, NULL
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
UNION ALL
-- Third level: OrderDetail elements - children of Order
SELECT 3 Tag
, 2 Parent
, Cust.CustomerID
, OrderHeader.SalesOrderID
, OrderHeader.STATUS
, SalesDetails.ProductID
, SalesDetails.UnitPrice
, SalesDetails.LineTotal
, SalesDetails.LineTotal / SalesDetails.UnitPrice Qty
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail SalesDetails
ON OrderHeader.SalesOrderID = SalesDetails.SalesOrderID
ORDER BY [Customer!1!CustomerID]
, [Order!2!SalesOrderID!ELEMENT]
, [OrderDetail!3!ProductID!ELEMENT]
FOR XML EXPLICIT; This query allows us to generate an element-centric XML while keeping full control of the element selection unlike FOR XML AUTO.
The resulting XML file looks like this, where we have:
- An element for each customer (red)
- nested element/s under the customer for each order (green)
- nested element/s under each order for order details (blue)
To make better sense of how this more advanced query works, let us execute it without the FOR XML EXPLICIT clause. Then, if we examine the first customer with ID 11000, we can clearly see the hierarchical structure serving as the basis for the resulting XML:
Querying native XML
Next, let us examine how to query an XML data type column. We will use the Person table to demonstrate this approach. Let us first see what the XML content called AdditionalContactInfo looks like:
SELECT BusinessEntityID,
AdditionalContactInfo
FROM Person.Person
WHERE AdditionalContactInfo IS NOT NULL 
Expanding the XML content in Azure Data Studio:
Using XMLNameSpaces
Now, let us say we wanted to get the address details from this XML, e.g. Street, City, StateProvince, PostalCode, CountryRegion, and SpecialInstructions:
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS
act)
SELECT BusinessEntityID
, Address.value('act:Street[1]', 'varchar(100)') Street
, Address.value('act:City[1]', 'varchar(50)') City
, Address.value('act:StateProvince[1]', 'varchar(10)') State
, Address.value('act:PostalCode[1]', 'varchar(10)') PostalCode
, Address.value('act:CountryRegion[1]', 'varchar(50)') Country
, Address.value('string(act:SpecialInstructions[1])', 'varchar(500)') AddressInstructions
FROM Person.Person
CROSS APPLY AdditionalContactInfo.nodes('//act:homePostalAddress') AddressTable(Address) First, we declare the xmlnamespace to use. It will be referenced throughout the query to extract the XML elements from the Adventure Works schema. This schema has been predefined and describes the structure of the XML tree. Next, for each column, we use the .value() method to extract a value from an XML instance stored in an xml type column, parameter, or variable. The .value() method follows the syntax: value ( XQuery , SQLType ) where the XQuery expression retrieves the data from the XML element and SQLType is the data type to be returned. Finally, we use CROSS APPLY to join each person record with the XML nodes extracted from their AdditionalContactInfo column. Using cross apply:
- joins each row with the xml content
- Inside the value() calls, uses the relative nested paths since we are already positioned at the target node (element)
- properly handles SpecialInstructions elements that contain both text and nested elements
- does not return any null rows where the AdditionalContent column itself is null or the xml content is different.
The .nodes() method on AdditionalContent allows us to identify nodes to be mapped to a new row following the syntax nodes (XQuery) as Table(Column). The XQuery path here corresponds to our target element homePostalAddress, highlighted in green. The double forward slash // means searching at any level of the XML hierarchy. Here is the result set:
If we examine any of the BusinessEntityIDs that do have XML content in the AdditionalContactInfo column, such as 297 or 299, but did not get included in the result set, we can see that neither has an element homePostalAddress. Therefore, no rows containing those IDs were returned.
XML to JSON
Finally, if we wanted to query an XML column but get the result set as a JSON object, we can reuse the previous query with a tiny modification – we add the FOR JSON AUTO clause:
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS act )
SELECT BusinessEntityID
, Address.value('act:Street[1]', 'varchar(100)') Street
, Address.value('act:City[1]', 'varchar(50)') City
, Address.value('act:StateProvince[1]', 'varchar(10)') State
, Address.value('act:PostalCode[1]', 'varchar(10)') PostalCode
, Address.value('act:CountryRegion[1]', 'varchar(50)') Country
, Address.value('string(act:SpecialInstructions[1])', 'varchar(500)') AddressInstructions
FROM Person.Person
CROSS APPLY AdditionalContactInfo.nodes('//act:homePostalAddress') AS AddressTable(Address) FOR JSON AUTO;
Clicking on the resulting JSON object and viewing it formatted we can see a neatly formatted result set in pure JSON:
Conclusion
In this article we examined several different ways of converting a SELECT statement to XML output. We also investigated how to query a native XML column and convert the result to JSON. Stay tuned for the next article in the series where we will deep dive into working with JSON in SQL Server 2025.
Next Steps
