---
title: "Examples: Using PATH Mode | Microsoft Docs"
ms.custom: ""
ms.date: "06/13/2017"
ms.prod: "sql-server-2014"
ms.reviewer: ""
ms.technology: xml
ms.topic: conceptual
helpviewer_keywords:
- "PATH FOR XML mode, examples"
ms.assetid: 3564e13b-9b97-49ef-8cf9-6a78677b09a3
author: MightyPen
ms.author: genemi
manager: craigg
---
# Examples: Using PATH Mode
The following examples illustrate the use of PATH mode in generating XML from a SELECT query. Many of these queries are specified against the bicycle manufacturing instructions XML documents that are stored in the Instructions column of the ProductModel table.
## Specifying a simple PATH mode query
This query specifies a FOR XML PATH mode.
```
USE AdventureWorks2012;
GO
SELECT
ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO
```
The following result is element-centric XML where each column value in the resulting rowset is wrapped in an element. Because the `SELECT` clause does not specify any aliases for the column names, the child element names generated are the same as the corresponding column names in the `SELECT` clause. For each row in the rowset a <`row`> tag is added.
``
`122`
`All-Purpose Bike Stand`
`
`
``
`119`
`Bike Wash`
`
`
The following result is the same as the `RAW` mode query with the `ELEMENTS` option specified. It returns element-centric XML with a default <`row`> element for each row in the result set.
```
USE AdventureWorks2012;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;
```
You can optionally specify the row element name to overwrite the default <`row`>. For example, the following query returns the <`ProductModel`> element for each row in the rowset.
```
USE AdventureWorks2012;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO
```
The resulting XML will have a specified row element name.
``
`122`
`All-Purpose Bike Stand`
``
``
`119`
`Bike Wash`
``
If you specify a zero-length string, the wrapping element is not produced.
```
USE AdventureWorks2012;
GO
SELECT ProductModelID,
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO
```
This is the result:
`122`
`All-Purpose Bike Stand`
`119`
`Bike Wash`
## Specifying XPath-like column names
In the following query the `ProductModelID` column name specified starts with '\@' and does not contain a slash mark ('/'). Therefore, an attribute of the <`row`> element that has the corresponding column value is created in the resulting XML.
```
USE AdventureWorks2012;
GO
SELECT ProductModelID AS "@id",
Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO
```
This is the result:
`< ProductModelData id="122">`
`All-Purpose Bike Stand`
``
`< ProductModelData id="119">`
`Bike Wash`
``
You can add a single top-level element by specifying the `root` option in `FOR XML`.
```
SELECT ProductModelID AS "@id",
Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO
```
To generate a hierarchy, you can include PATH-like syntax. For example, change the column name for the `Name` column to "SomeChild/ModelName" and you will obtain XML with hierarchy, as shown in this result:
``
``
``
`All-Purpose Bike Stand`
``
``
``
``
`Bike Wash`
``
``
``
Besides the product model ID and name, the following query retrieves the manufacturing instruction locations for the product model. Because the Instructions column is of `xml` type, the `query()` method of `xml` data type is specified to retrieve the location.
```
SELECT ProductModelID AS "@id",
Name,
Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location
') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO
```
This is the partial result. Because the query specifies ManuInstr as the column name, the XML returned by the `query()` method is wrapped in a <`ManuInstr`> tag as shown in the following:
``
``
`HL Touring Frame`
``
`......`
``
`...`
``
``
``
In the previous FOR XML query, you may want to include namespaces for the <`Root`> and <`ProductModelData`> elements. You can do this by first defining the prefix to namespace binding by using WITH XMLNAMESPACES and using prefixes in the FOR XML query. For more information, see [Add Namespaces to Queries with WITH XMLNAMESPACES](add-namespaces-to-queries-with-with-xmlnamespaces.md).
```
USE AdventureWorks2012;
GO
WITH XMLNAMESPACES (
'uri1' AS ns1,
'uri2' AS ns2,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
Name AS "ns1:Name",
Instructions.query('
/MI:root/MI:Location
')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO
```
Note that the `MI` prefix is also defined in the `WITH XMLNAMESPACES`. As a result, the `query()` method of the `xml` type specified does not define the prefix in the query prolog. This is the result:
``
``
`7`
`HL Touring Frame`
``
``
`Insert aluminum sheet MS-2341 into the T-85A framing tool.`
``
`...`
``
`...`
``
``
## Generating a value list using PATH mode
For each product model, this query constructs a value list of product IDs. For each product ID, the query also constructs <`ProductName`> nested elements, as shown in this XML fragment:
``
`...`
`...`
`...`
``
This is the query that produces the XML you want:
```
USE AdventureWorks2012;
GO
SELECT ProductModelID AS "@ProductModelID",
Name S "@ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) S "@ProductIDs",
(SELECT Name AS "ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) as "ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');
```
Note the following from the previous query:
- The first nested `SELECT` returns a list of ProductIDs by using `data()` as the column name. Because the query specifies an empty string as the row element name in `FOR XML PATH`, no element is generated. Instead, the value list is assigned to the `ProductID` attribute.
- The second nested `SELECT` retrieves product names for products in the product model. It generates <`ProductName`> elements that are returned wrapped in the <`ProductNames`> element, because the query specifies `ProductNames` as the column name.
This is the partial result:
``
``
`HL Touring Frame - Yellow, 60`
`HL Touring Frame - Yellow, 46`
`...`
``
``
``
`LL Road Frame - Black, 58`
`LL Road Frame - Black, 60`
`LL Road Frame - Black, 62`
`...`
``
``
The subquery constructing the product names returns the result as a string that is entitized and then added to the XML. If you add the type directive, `FOR XML PATH (''), type`, the subquery returns the result as `xml` type and no entitization occurs.
```
USE AdventureWorks2012;
GO
SELECT ProductModelID AS "@ProductModelID",
Name AS "@ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ProductIDs",
(
SELECT Name AS "ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH (''), type
) AS "ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');
```
## Adding namespaces in the resulting XML
As described in [Adding Namespaces Using WITH XMLNAMESPACES](add-namespaces-to-queries-with-with-xmlnamespaces.md), you can use WITH XMLNAMESPACES to include namespaces in the PATH mode queries. For example, names specified in the SELECT clause include namespace prefixes. The following `PATH` mode query constructs XML with namespaces.
```
SELECT 'en' as "English/@xml:lang",
'food' as "English",
'ger' as "German/@xml:lang",
'Essen' as "German"
FOR XML PATH ('Translation')
GO
```
The `@xml:lang` attribute added to the <`English`> element is defined in the predefined xml namespace.
This is the result:
``
`food`
`Essen`
``
The following query is similar to example C, except that it uses `WITH XMLNAMESPACES` to include namespaces in the XML result. For more information, see [Add Namespaces to Queries with WITH XMLNAMESPACES](add-namespaces-to-queries-with-with-xmlnamespaces.md).
```
USE AdventureWorks2012;
GO
WITH XMLNAMESPACES ('uri1' AS ns1, DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
Name AS "@ns1:ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ns1:ProductIDs",
(
SELECT ProductID AS "@ns1:ProductID",
Name AS "@ns1:ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH , type
) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');
```
This is the result:
``
``
``
`|
`
`|
`
`...`
``
``
``
``
`|
`
`...`
``
``
``
## See Also
[Use PATH Mode with FOR XML](use-path-mode-with-for-xml.md)