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:
SELECT * FROM Table1 WHERE ......(SELECT * FROM Table2 FOR XML RAW)
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:
When ServerName is a network server, the query returns:
This can be avoided by specifying this alias:
SELECT TOP 1 LastName FROM ServerName.Northwind.dbo.Employees x FOR XML AUTO
This query returns:
- 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"/>