---
title: FOR XML (SQL Server)
description: Learn how to retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.
author: MikeRayMSFT
ms.author: mikeray
ms.reviewer: randolphwest
ms.date: 05/24/2024
ms.service: sql
ms.subservice: xml
ms.topic: conceptual
f1_keywords:
  - "FOR_XML_TSQL"
helpviewer_keywords:
  - "FOR XML clause, about FOR XML clause"
  - "PATH FOR XML mode, construction"
  - "EXPLICIT FOR XML mode"
  - "RAW FOR XML mode"
  - "retrieving XML data"
  - "XML [SQL Server], FOR XML clause"
  - "AUTO FOR XML mode"
  - "XML [SQL Server], construction"
dev_langs:
  - TSQL
---
# FOR XML (SQL Server)

[!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sql-asdb-asdbmi.md)]

A `SELECT` query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the `FOR XML` clause in the query. The `FOR XML` clause can be used in top-level queries and in subqueries. The top-level `FOR XML` clause can be used only in the `SELECT` statement. In subqueries, `FOR XML` can be used in the `INSERT`, `UPDATE`, and `DELETE` statements. `FOR XML` can also be used in assignment statements.

In a `FOR XML` clause, you specify one of these modes:

- `RAW`
- `AUTO`
- `EXPLICIT`
- `PATH`

The `RAW` mode generates a single `<row>` element per row in the rowset that is returned by the `SELECT` statement. You can generate XML hierarchy by writing nested `FOR XML` queries.

The `AUTO` mode generates nesting in the resulting XML by using heuristics based on the way the `SELECT` statement is specified. You have minimal control over the shape of the XML generated. The nested `FOR XML` queries can be written to generate XML hierarchy beyond the XML shape that is generated by `AUTO` mode heuristics.

The `EXPLICIT` mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of `EXPLICIT` mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, the `OrderID` attribute might have a list of order ID values), and mixed contents.

However, writing `EXPLICIT` mode queries can be cumbersome. You can use some of the new `FOR XML` capabilities, such as writing nested `FOR XML RAW`, `AUTO`, or `PATH` mode queries and the `TYPE` directive, instead of using `EXPLICIT` mode to generate the hierarchies. The nested `FOR XML` queries can produce any XML that you can generate by using the `EXPLICIT` mode. For more information, see [Use Nested FOR XML Queries](use-nested-for-xml-queries.md) and [TYPE Directive in FOR XML Queries](type-directive-in-for-xml-queries.md).

The `PATH` mode together with the nested `FOR XML` query capability provides the flexibility of the `EXPLICIT` mode in a simpler manner.

These modes are in effect only for the execution of the query for which they're set. They don't affect the results of any subsequent queries.

`FOR XML` isn't valid for any selection that is used with a `FOR BROWSE` clause.

## Examples

The following `SELECT` statement retrieves information from the `Sales.Customer` and `Sales.SalesOrderHeader` tables in the [!INCLUDE [sssampledbobject-md](../../includes/sssampledbobject-md.md)] database. This query specifies the `AUTO` mode in the `FOR XML` clause:

```sql
USE AdventureWorks2022;
GO

SELECT Cust.CustomerID,
    OrderHeader.CustomerID,
    OrderHeader.SalesOrderID,
    OrderHeader.STATUS
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
    ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO;
```

## The FOR XML clause and server names

When a `SELECT` statement with a `FOR XML` clause specifies a four-part name in the query, the server name isn't returned in the resulting XML document when the query is executed on the local computer. However, the server name is returned as the four-part name when the query is executed on a network server.

For example, consider this query:

```sql
SELECT TOP 1 LastName
FROM ServerName.AdventureWorks2022.Person.Person
FOR XML AUTO;
```

**Local server**: When `ServerName` is a local server, the query returns the following text:

```xml
<AdventureWorks2022.Person.Person LastName="Achong" />
```

**Network server**: When `ServerName` is a network server, the query returns the following text:

```xml
<ServerName.AdventureWorks2022.Person.Person LastName="Achong" />
```

**Avoid ambiguity**: This potential ambiguity can be avoided by specifying this alias:

```sql
SELECT TOP 1 LastName
FROM ServerName.AdventureWorks2022.Person.Person x
FOR XML AUTO;
```

Now the disambiguated query returns the following text:

```xml
<x LastName="Achong"/>
```

## Related content

- [Basic Syntax of the FOR XML Clause](basic-syntax-of-the-for-xml-clause.md)
- [Use RAW Mode with FOR XML](use-raw-mode-with-for-xml.md)
- [Use AUTO Mode with FOR XML](use-auto-mode-with-for-xml.md)
- [Use EXPLICIT Mode with FOR XML](use-explicit-mode-with-for-xml.md)
- [Use PATH Mode with FOR XML](use-path-mode-with-for-xml.md)
- [OPENXML (SQL Server)](openxml-sql-server.md)
- [Add Namespaces to Queries with WITH XMLNAMESPACES](add-namespaces-to-queries-with-with-xmlnamespaces.md)