Using PATH Mode

Artigo
12/03/2008

As described in Constructing XML Using FOR XML, the PATH mode provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a rowset, but the PATH mode provides a simpler alternative to the potentially cumbersome EXPLICIT mode queries. PATH mode, together with the ability to write nested FOR XML queries and the TYPE directive to return xml type instances, allows you to write queries with less complexity.

In PATH mode, column names or column aliases are treated as XPath expressions. These expressions indicate how the values are being mapped to XML. Each XPath expression is a relative XPath that provides the item type., such as the attribute, element, and scalar value, and the name and hierarchy of the node that will be generated relative to the row element.

This topic describes the following conditions for mapping columns in a rowset:

Columns without a name
Columns with a name
Columns with a name specified as a wildcard character (*)
Columns with the name of an XPath Node Test
Column names with the path specified as data()
Columns that contain a NULL value by default

Columns without a Name

Any column without a name will be inlined. For example, computed columns or nested scalar queries that do not specify column alias will generate columns without any name. If the column is of xml type, the content of that data type instance is inserted. Otherwise, the column content is inserted as a text node.

        SELECT 2+2
FOR XML PATH

Produce this XML. By default, for each row in the rowset, a <row> element is generated in the resulting XML. This is the same as RAW mode.

<row>4</row>

The following query returns a three-column rowset. The third column without a name has XML data. The PATH mode inserts an instance of the xml type.

SELECT ProductModelID,
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location 
              ') 
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH 
go

This is the partial result:

<row>
  <ProductModelID>7</ProductModelID>
  <Name>HL Touring Frame</Name>
  <MI:Location ...LocationID="10" ...></MI:Location>
  <MI:Location ...LocationID="20" ...></MI:Location>
   ...
</row>

Columns with a Name

Following are the specific conditions in which rowset columns with a name are mapped, case-sensitive, to the resulting XML:

Column name starts with an at sign (@)
Column name does not start with an at sign (@)
Column name does not start with an at sign@ and contains a slash mark (/)
Several columns share the same prefix
One column has a different name

Column Name Starts with an At Sign (@)

If the column name starts with an at sign (@) and does not contain a slash mark (/), an attribute of the <row> element that has the corresponding column value is created. For example, the following query returns a two-column (@PmId, Name) rowset. In the resulting XML, a PmId attribute is added to the corresponding <row> element and a value of ProductModelID is assigned to it.

                SELECT ProductModelID as "@PmId",
       Name
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH 
go

This is the result:

<row PmId="7">
  <Name>HL Touring Frame</Name>
</row>

Note that attributes must come before any other node types, such as element nodes and text nodes, in the same level. The following query will return an error:

SELECT Name,
       ProductModelID as "@PmId"
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH 
go

Column Name Does Not Start with an At Sign (@)

If the column name does not start with an at sign (@), is not one of the XPath node tests, and does not contain a slash mark (/), an XML element that is a subelement of the row element, <row> by default, is created.

The following query specifies the column name, the result. Therefore, a <result> element child is added to the <row> element.

SELECT 2+2 as result
for xml PATH

This is the result:

<row>
  <result>4</result>
</row>

The following query specifies the column name, ManuWorkCenterInformation, for the XML returned by the XQuery specified against Instructions column of xml type. Therefore, a <ManuWorkCenterInformation> element is added as a child of the <row> element.

SELECT 
       ProductModelID,
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location 
              ') as ManuWorkCenterInformation
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH 
go

This is the result:

<row>
  <ProductModelID>7</ProductModelID>
  <Name>HL Touring Frame</Name>
  <ManuWorkCenterInformation>
    <MI:Location ...LocationID="10" ...></MI:Location>
    <MI:Location ...LocationID="20" ...></MI:Location>
     ...
  </ManuWorkCenterInformation>
</row>

Column Name Does Not Start with an At Sign (@) and Contains a Slash Mark (/)

If the column name does not start with an at sign (@), but contains a slash mark (/), the column name indicates an XML hierarchy. For example, if the column name is "Name1/Name2/Name3.../Namen ", each Namei represents an element name that is nested in the current row element (for i=1) or that is under the element that has the name Namei-1. If Namen starts with '@', it is mapped to an attribute of Namen-1 element.

For example, the following query returns an employee ID and name that are represented as a complex element EmpName that contains a First, Middle, and Last name.

SELECT EmployeeID "@EmpID", 
       FirstName  "EmpName/First", 
       MiddleName "EmpName/Middle", 
       LastName   "EmpName/Last"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID
AND    E.EmployeeID=1
FOR XML PATH

The column names are used as a path in constructing XML in the PATH mode. The column name that contains employee ID values, starts with '@'.Therefore, an attribute, EmpID, is added to the <row> element. All other columns include a slash mark ('/') in the column name that indicates hierarchy. The resulting XML will have the <EmpName> child under the <row> element, and the <EmpName> child will have <First>, <Middle> and <Last> element children.

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Last>Achong</Last>
  </EmpName>
</row>

The employee middle name is null and, by default, the null value maps to the absence of the element or attribute. If you want elements generated for the NULL values, you can specify the ELEMENTS directive with XSINIL as shown in this query.

SELECT EmployeeID "@EmpID", 
       FirstName  "EmpName/First", 
       MiddleName "EmpName/Middle", 
       LastName   "EmpName/Last"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID
AND    E.EmployeeID=1
FOR XML PATH, ELEMENTS XSINIL

This is the result:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Middle xsi:nil="true" />
    <Last>Achong</Last>
  </EmpName>
</row>

By default, the PATH mode generates element-centric XML. Therefore, specifying the ELEMENTS directive in a PATH mode query has no effect. However, as shown in the previous example, the ELEMENTS directive is useful with XSINIL to generate elements for null values.

Besides the ID and name, the following query retrieves an employee address. As per the path in the column names for address columns, an <Address> element child is added to the <row> element and the address details are added as element children of the <Address> element.

SELECT EmployeeID   "@EmpID", 
       FirstName    "EmpName/First", 
       MiddleName   "EmpName/Middle", 
       LastName     "EmpName/Last",
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City         "Address/City"
FROM   HumanResources.Employee E, Person.Contact C, Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID=1
FOR XML PATH

This is the result:

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Last>Achong</Last>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <City>Monroe</City>
  </Address>
</row>

If several subsequent columns share the same path prefix, they are grouped together under the same name. If different namespace prefixes are being used even if they are bound to the same namespace, a path is considered different. In the previous query, the FirstName, MiddleName, and LastName columns share the same EmpName prefix.Therefore, they are added as children of the <EmpName> element. This is also the case when you were creating the <Address> element in the previous example.

One Column Has a Different Name

If a column with a different name appears in between, it will break the grouping, as shown in the following modified query. The query breaks the grouping of FirstName, MiddleName, and LastName, as specified in the previous query, by adding address columns in between the FirstName and MiddleName columns.

SELECT EmployeeID "@EmpID", 
       FirstName "EmpName/First", 
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City "Address/City",
       MiddleName "EmpName/Middle", 
       LastName "EmpName/Last"
FROM   HumanResources.EmployeeAddress E, Person.Contact C, Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID=1
FOR XML PATH

As a result, the query creates two <EmpName> elements. The first <EmpName> element has the <FirstName> element child and the second <EmpName> element has the <MiddleName> and <LastName> element children.

This is the result:

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <City>Monroe</City>
  </Address>
  <EmpName>
    <Last>Achong</Last>
  </EmpName>
</row>

Columns with a Name Specified As a Wildcard Character

If the column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified. If this column is a non-xml type column, the column content is inserted as a text node, as shown in the following example:

SELECT EmployeeID "@EmpID", 
       FirstName "*", 
       MiddleName "*", 
       LastName "*"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID
AND    E.EmployeeID=1
FOR XML PATH

This is the result:

<row EmpID="1">GustavoAchong</row>

If the column is of xml type, the corresponding XML tree is inserted. For example, the following query specifies "*" for the column name that contains the XML returned by the XQuery against the Instructions column.

SELECT 
       ProductModelID,
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
                /MI:root/MI:Location 
              ') as "*"
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH 
go

This is the result. The XML returned by XQuery is inserted without a wrapping element.

<row>
  <ProductModelID>7</ProductModelID>
  <Name>HL Touring Frame</Name>
  <MI:Location LocationID="10">...</MI:Location>
  <MI:Location LocationID="20">...</MI:Location>
...
</row>

Columns with the Name of an XPath Node Test

If the column name is one of the XPath node tests, the content is mapped as shown in the following table.

When the column name is an XPath node test, the content is mapped to the corresponding node. If the SQL type of the column is xml, an error is returned.

Column Name	Behavior
text()	For a column with the name of text(), the string value in that column is added as a text node.
comment()	For a column with the name of comment(), the string value in that column is added as an XML comment.
node()	For a column with the name of node(), the result is the same as it is when the column name is a wildcard character (*).
processing-instruction(name)	For a column with the name of a processing instruction, the string value in that column is added as the PI value for the processing instruction target name.

Columns Names with the Path Specified As data()

If the path specified as column name is "data()", the value is treated as an atomic value in the generated XML. A space character is added to the XML if the next item in the serialization is also an atomic value. This is useful when you are creating list typed element and attribute values. The following query retrieves the product model ID, name, and list of products in that product model.

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"
FROM  Production.ProductModel
WHERE ProductModelID= 7 
FOR XML PATH('ProductModelData')

The nested SELECT retrieves a list of product IDs. It specifies "data()" as the column name for product IDs. Because PATH mode specifies an empty string for the row element name, there is no row element generated. Instead, the values are returned as assigned to a ProductIDs attribute of the <ProductModelData> row element of the parent SELECT. This is the result:

<ProductModelData ProductModelID="7" 
                  ProductModelName="HL Touring Frame" 
                  ProductIDs="885 887 888 889 890 891 892 893" />

Columns that Contain a Null Value By Default

By default, a null value in a column maps to the absence of the attribute, node, or element. This default behavior can be overwritten by requesting element-centric XML using the ELEMENTS directive and specifying XSINIL to request adding elements for NULL values, as shown in the following query:

SELECT EmployeeID as "@EmpID", 
       FirstName  as "EmpName/First", 
       MiddleName as "EmpName/Middle", 
       LastName   as "EmpName/Last"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID
AND    E.EmployeeID=1
FOR XML PATH, ELEMENTS XSINIL

The following shows the result. Note that if XSINIL is not specified, the <Middle> element will be absent.

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Middle xsi:nil="true" />
    <Last>Achong</Last>
  </EmpName>
</row>

Namespace support

In this release, namespace support in the PATH mode is provided by using WITH NAMESPACES. For example, the following query demonstrates the WITH NAMESPACES syntax to declare a namespace ("a:") that can then be used in the subsequent SELECT statement:

WITH XMLNAMESPACES('a' as a)
SELECT 1 as 'a:b'
FOR XML PATH

Examples

These samples 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. For more information about the XML instructions, see xml Data Type Representation in the AdventureWorks Database.

A. Specifying a simple PATH mode query

This query specifies a FOR XML PATH mode.

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.

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

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.

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.

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.

<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</ProductModel>

If you specify a zero-length string, the wrapping element is not produced.

SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('')
Go

This is the result:

<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>

B. 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.

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">
    <Name>All-Purpose Bike Stand</Name>
</ ProductModelData >
< ProductModelData id="119">
    <Name>Bike Wash</Name>
</ ProductModelData >

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:

<Root>
  <ProductModelData id="122">
    <SomeChild>
      <ModelName>All-Purpose Bike Stand</ModelName>
    </SomeChild>
  </ProductModelData>
  <ProductModelData id="119">
    <SomeChild>
      <ModelName>Bike Wash</ModelName>
    </SomeChild>
  </ProductModelData>
</Root>

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:

<Root>
  <ProductModelData id="7">
    <Name>HL Touring Frame</Name>
    <ManuInstr>
      <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
        <MI:step>...</MI:step>...
      </MI:Location>
      ...
    </ManuInstr>
  </ProductModelData>
</Root>

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 Adding Namespaces Using WITH XMLNAMESPACES.

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:

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
  <ns2:ProductInfo>
    <ns1:ProductModelID>7</ns1:ProductModelID>
    <ns1:Name>HL Touring Frame</ns1:Name>
    <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
       LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" >
       <MI:step>
          Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>. 
       </MI:step>
         ...
    </MI:Location>
     ...
  </ns2:ProductInfo>
</ns1:root>

C. 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:

<ProductModelData ProductModelID="7" ProductModelName="..."  
                  ProductIDs="product id list in the product model" >
  <ProductName>...</ProductName>
  <ProductName>...</ProductName>
  ...
</ProductModelData>

This is the query that produces the XML you want:

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

<ProductModelData PId="7" 
                  ProductModelName="HL Touring Frame" 
                  ProductIDs="885 887 ...">
  <ProductNames>
    &lt;ProductName&gt;HL Touring Frame - Yellow, 60&lt;/ProductName&gt;
    &lt;ProductName&gt;HL Touring Frame - Yellow, 46&lt;/ProductName&gt;</ProductNames>
    ...
</ProductModelData>
<ProductModelData PId="9" 
                  ProductModelName="LL Road Frame" 
                  ProductIDs="722 723 724 ...">
  <ProductNames>
     &lt;ProductName&gt;LL Road Frame - Black, 58&lt;/ProductName&gt;
     &lt;ProductName&gt;LL Road Frame - Black, 60&lt;/ProductName&gt;
     &lt;ProductName&gt;LL Road Frame - Black, 62&lt;/ProductName&gt;
     ...
  </ProductNames>
</ProductModelData>

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.

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

D. Adding namespaces in the resulting XML

As described in Adding Namespaces Using WITH XMLNAMESPACES, 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:

<Translation>
  <English xml:lang="en">food</English>
  <German xml:lang="ger">Essen</German>
</Translation>

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 Adding Namespaces Using WITH XMLNAMESPACES.

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:

<root xmlns="uri2" xmlns:ns1="uri1">
  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
       ...
    </ns1:ProductNames>
  </ProductModelData>
  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
        ...
    </ns1:ProductNames>
  </ProductModelData>
</root>

Partilhar via

Using PATH Mode

Columns without a Name

Columns with a Name

Column Name Starts with an At Sign (@)

Column Name Does Not Start with an At Sign (@)

Column Name Does Not Start with an At Sign (@) and Contains a Slash Mark (/)

One Column Has a Different Name

Columns with a Name Specified As a Wildcard Character

Columns with the Name of an XPath Node Test

Columns Names with the Path Specified As data()

Columns that Contain a Null Value By Default

Namespace support

Examples

A. Specifying a simple PATH mode query

B. Specifying XPath-like column names

C. Generating a value list using PATH mode

D. Adding namespaces in the resulting XML

See Also

Reference

Concepts

Other Resources

Help and Information

Recursos adicionais

Partilhar via

Using PATH Mode

Columns without a Name

Columns with a Name

Column Name Starts with an At Sign (@)

Column Name Does Not Start with an At Sign (@)

Column Name Does Not Start with an At Sign (@) and Contains a Slash Mark (/)

Several Columns Share the Same Path Prefix

One Column Has a Different Name

Columns with a Name Specified As a Wildcard Character

Columns with the Name of an XPath Node Test

Columns Names with the Path Specified As data()

Columns that Contain a Null Value By Default

Namespace support

Examples

A. Specifying a simple PATH mode query

B. Specifying XPath-like column names

C. Generating a value list using PATH mode

D. Adding namespaces in the resulting XML

See Also

Reference

Concepts

Other Resources

Help and Information

Recursos adicionais