Guidelines for Using the FOR XML Clause

XML and Internet Support

Guidelines for Using the FOR XML Clause

The FOR XML clause is valid only in the SELECT statement and is subject to these limitations:

FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other statements (SELECT INTO, assignment). For example, subselects as shown in these examples are not supported:
Example A
```
SELECT *
FROM Table1
WHERE ......(SELECT * FROM Table2 FOR XML RAW)
```
Example B
```
DECLARE @doc nchar(3000)
SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML RAW)
```
FOR XML is not valid for any selection that is used with a COMPUTE BY or FOR BROWSE clause, for example:
```
SELECT OrderID, UnitPrice 
FROM [Order Details] 
ORDER BY OrderID COMPUTE SUM(UnitPrice) BY OrderID
```
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO. For example:
```
SELECT max(price), min(price), avg(price)
FROM titles
FOR XML AUTO
```
FOR XML is not valid in a SELECT statement used in a view definition or in a user-defined function that returns a rowset. For example, this statement is not allowed:
```
CREATE VIEW AllOrders AS SELECT * FROM Orders FOR XML AUTO
```
However, a statement such as the following is allowed:
```
SELECT * FROM ViewName FOR XML AUTO are allowed.
```
FOR XML cannot be used in a selection that requires further processing in a stored procedure.
FOR XML cannot be used with cursors.
Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client.
FOR XML cannot be used in a stored procedure when called in an INSERT statement.
When a SELECT statement with a FOR XML clause specifies a four-part name in the query, the server name is not 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:
```
SELECT TOP 1 LastName
FROM ServerName.Northwind.dbo.Employees
FOR XML AUTO
```
When ServerName is a local server, the query returns:
```
<Northwind.dbo.Employees LastName="Buchanan"/>
```
When ServerName is a network server, the query returns:
```
<ServerName.Northwind.dbo.Employees LastName="Buchanan"/>
```
This can be avoided by specifying this alias:
```
SELECT TOP 1 LastName
FROM ServerName.Northwind.dbo.Employees x
FOR XML AUTO 
```
This query returns:
```
<x ="Buchanan"/>
```

Using derived tables in a SELECT statement with FOR XML AUTO may not produce the nesting you want.

The FOR BROWSE mode is implemented when a query with the FOR XML AUTO mode is specified. The FOR XML AUTO mode uses the information provided by the FOR BROWSE mode in determining the hierarchy in the result set.

For example, consider the following query. A derived table P is created in the query.

SELECT c.CompanyName,
       o.OrderID,
       o.OrderDate,
       p.ProductName,
       p.Quantity,
       p.UnitPrice,
       p.Total
FROM   Customers AS c
       JOIN
       Orders AS o
       ON
       c.CustomerID = o.CustomerID
       JOIN
       (
         SELECT od.OrderID,
                pr.ProductName,
                od.Quantity,
                od.UnitPrice,
                od.Quantity * od.UnitPrice AS total
         FROM   Products AS pr
                JOIN
                [Order Details] AS od
                ON
                pr.ProductID = od.ProductID
       ) AS p
       ON
       o.OrderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName="Vins et alcools Chevalier">
  <o OrderID="10248" OrderDate="1996-07-04T00:00:00">
    <pr ProductName="Queso Cabrales">
        <od Quantity="12" UnitPrice="14.0000" total="168.0000"/>
    </pr>
    <pr ProductName="Singaporean Hokkien Fried Mee">
        <od Quantity="10" UnitPrice="9.8000" total="98.0000"/>
    </pr>
</c>

In the resulting XML document, the <p> element is missing, and the <pr> and <od> elements are returned. This occurs because the query optimizer eliminates the P table in the result and returns a result set consisting of the od and pr tables.

This can be avoided by rewriting the query. For example, you can rewrite the query is to create a view and use it in the SELECT statement:

CREATE VIEW p AS  
         SELECT od.OrderID,
                pr.ProductName,
                od.Quantity,
                od.UnitPrice,
                od.Quantity * od.UnitPrice AS total
         FROM   Products AS pr
                JOIN
                [Order Details] AS od
                ON
                pr.ProductID = od.ProductID

And then write the SELECT statement:

SELECT c.CompanyName,
       o.OrderID,
       o.OrderDate,
       p.ProductName,
       p.Quantity,
       p.UnitPrice,
       p.total
FROM   Customers AS c
       JOIN
       Orders AS o
       ON
       c.CustomerID = o.CustomerID
       JOIN
        p
       ON
       o.OrderID = p.OrderID
FOR XML AUTO

This is the partial result:

<c CompanyName="Vins et alcools Chevalier">
  <o OrderID="10248" OrderDate="1996-07-04T00:00:00">
    <p ProductName="Queso Cabrales" 
       Quantity="12" 
       UnitPrice="14.0000" 
       total="168.0000"/>
  </o>
</c>

In addition, SQL Server names containing characters that are invalid in XML names (such as spaces) are translated into XML names in a way in which the invalid characters are translated into escaped numeric entity encoding.

There are only two nonalphabetic characters that can begin an XML name: the colon (:) and the underscore (_). Because the colon (:) is already reserved for namespaces, the underscore (_) is chosen as the escape character. The escape rules used for encoding are:

Any UCS-2 character that is not a valid XML name character (according to the XML 1.0 specification) is escaped as _xHHHH_, where HHHH stands for the four-digit hexadecimal UCS-2 code for the character in the most significant bit-first order. For example, the table name Order Details is encoded as Order_x0020_Details.
Characters that do not fit into the UCS-2 realm (the UCS-4 additions of the range U+00010000 to U+0010FFFF) are encoded as _xHHHHHHHH_, where HHHHHHHH stands for the eight-digit hexadecimal UCS-4 encoding of the character.
The underscore character does not need to be escaped unless it is followed by the character x. For example, the table name Order_Details is not encoded.
The colon (:) in identifiers is not escaped so that the namespace element and attribute names can be generated by the FOR XML query. For example, the following query generates a namespace attribute with a colon in the name:
```
SELECT 'namespace-urn' as 'xmlns:namespace', 
         1 as 'namespace:a' 
FOR XML RAW
```
The query produces this result:
```
<row xmlns:namespace="namespace-urn" namespace:a="1"/>
```
In a SELECT query, casting of any column to a binary large object (BLOB) makes it a temporary entity (losing its associated table name and column name). This causes AUTO mode queries to generate an error because it does not know where to place this value in the XML hierarchy, for example:
```
CREATE TABLE MyTable (Col1 int PRIMARY KEY, Col2 binary)
INSERT INTO MyTable VALUES (1, 0x7)
```
This query produces an error because of the casting to a BLOB:
```
SELECT Col1,
         CAST(Col2 as image) as Col2
FROM MyTable
FOR XML AUTO
```
If you remove the casting, the query produces results as expected:
```
SELECT Col1,
         Col2
FROM MyTable
FOR XML AUTO
```
This is the result:
```
<Computed Col1="1" Col2="dbobject/Computed[@Col1='1']/@Col2"/> 
```

Guidelines for Using the FOR XML Clause

XML and Internet Support

Guidelines for Using the FOR XML Clause

See Also

Get in touch