FOR XML Limitations

There is quite a long list of limitations associated with FOR XML. If you are having trouble getting FOR XML to work the way you think it should at any time, refer to this list of limitations and make sure what you're trying to accomplish is not listed here.

SELECT Statements

The following SELECT statements using FOR XML are invalid:

• The use of COMPUTE, COMPUTE BY, and FOR BROWSE is invalid in SELECT using FOR XML. For example, the following is invalid:

   SELECT OrderID, Freight FROM Orders ORDER BY OrderID COMPUTE  AVG(Freight) for XML AUTO 

• The use of GROUP BY and aggregate functions is invalid in SELECT using FOR XML. The following won't work:

   SELECT COUNT(*) FROM Employees FOR XML RAW 

• FOR XML is not valid in compound SELECT statements, sub-selects, or SELECT INTO. The following sample code is invalid:

   SELECT * FROM Orders WHERE  (SELECT * FROM [Order Details] for XML  AUTO) 

• You cannot use FOR XML in SELECT statement inside a stored procedure if the returned data requires further processing.

CREATE VIEW Statements that Return Rowsets

FOR XML AUTO is not allowed in CREATE VIEW statements or in user -defined functions that return rowsets.The following is not allowed:

 CREATE VIEW SmallOrders AS SELECT * FROM Orders WHERE Quantity <= 10 FOR XML RAW 

But you can do this:

 SELECT * FROM SmallOrders FOR XML RAW 

FOR XML Quirk

A little quirk of FOR XML is that if you specify the servername .databasename. owner.tablename four-part name in a SELECT statement on a local computer, the server name isn't returned in the tag name.When executed on a network computer, the server name is returned. For example

 SELECT TOP 2 CustomerID FROM sqlserver.Northwind.dbo.Customers for XML AUTO 

returns

 <Northwind.dbo.Customers CustomerID ="ALFKI"/>  <Northwind.dbo.Customers CustomerID ="ANATR"/> 

when the query is performed locally on a server.

If the SQL server is a network server, the query returns the following:

 <sqlserver.Northwind.dbo.Customers CustomerID ="ALFKI"/>  <sqlserver.Northwind.dbo.Customers CustomerID ="ANATR"/> 

A workaround for this is to use an alias for the four-part name as follows :

 SELECT TOP 2 CustomerID FROM sqlserver.Northwind.dbo.Customers CustID for XML   AUTO 

The preceding query returns

 <CustID ="ALFKI"/>  <CustID ="ANATR"/> 

Derived Tables

FOR XML AUTO might not produce the exact nesting you want if you are using derived tables. See Listing 7.1 for the query utilizing a derived table. Listing 7.2 shows the partial results.

Listing 7.1 SELECT Statement Using a Derived Table

 SELECT c.ContactName,         o.OrderID,         p.ProductName,             call quantities from the derived table 'p'         p.UnitPrice,         p.Quantity  FROM    Customers as c        JOIN        Orders as o        ON        c.CustomerID = o.CustomerID        (SELECT od.OrderID,            create the derived table                 pr.ProductName,                 od.UnitPrice,                 od.Quantity          FROM   Products as pr                 JOIN                 [Order Details] as od                 ON                 pr.ProductID = od.ProductID) AS p        ON        o.OrderID = p.OrderID  FOR XML AUTO 

Listing 7.2 Partial Results of a Derived Table

 <?xml version="1.0" encoding="utf-8" ?>  <root>    <c ContactName="Maria Anders">      <o OrderID="10643">        <pr ProductName="Rssle Sauerkraut">          <od UnitPrice="45.6" Quantity="15"/>        </pr>        <pr ProductName="Chartreuse verte">          <od UnitPrice="18" Quantity="21"/>        </pr>        <pr ProductName="Spegesild">          <od UnitPrice="12" Quantity="2"/>        </pr>      </o>      <o OrderID="10692">        <pr ProductName="Vegie-spread">          <od UnitPrice="43.9" Quantity="20"/>        </pr>      </o>      <o OrderID="10702">        <pr ProductName="Aniseed Syrup">          <od UnitPrice="10" Quantity="6"/>        </pr>        <pr ProductName="Lakkalikri">          <od UnitPrice="18" Quantity="15"/>        </pr>      </o>      <o OrderID="10835">  ... 

Notice that the <p> element is missing and that the <pr> and <od> elements are returned. The query optimizer did this. You would expect the <c> , <o> , and <p> elements. Now that you know how this works, a workaround to obtain the proper element nesting is to rewrite the query. First create a view and then use it in the XML query. Listings 7.3, 7.4, and 7.5 show the VIEW statement, the rewritten query, and the partial results, respectively.

Listing 7.3 CREATE VIEW Statement

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

Listing 7.4 Query Utilizing the VIEW

 SELECT c.ContactName,         o.OrderID,         p.ProductName,                 call quantities from the view 'p'         p.UnitPrice,         p.Quantity  FROM Customers AS c         JOIN         Orders AS o         ON         c.CustomerID = o.CustomerID         JOIN         p         ON         o.OrderID = p.OrderID 

Listing 7.5 Partial Results of the Rewrite

 <?xml version="1.0" encoding="utf-8" ?>  <root>    <c ContactName="Maria Anders">      <o OrderID="10643">        <p ProductName="Rssle Sauerkraut" UnitPrice="45.6" Quantity="15" />        <p ProductName="Chartreuse verte" UnitPrice="18" Quantity="21" />        <p ProductName="Spegesild" UnitPrice="12" Quantity="2" />      </o>      <o OrderID="10692">        <p ProductName="Vegie-spread" UnitPrice="43.9" Quantity="20" />      </o>      <o OrderID="10702">        <p ProductName="Aniseed Syrup" UnitPrice="10" Quantity="6" />        <p ProductName="Lakkalikri" UnitPrice="18" Quantity="15" />      </o>      <o OrderID="10835">  ... 

CAST Operator

Utilizing the CAST operator on a BLOB entity causes an error in AUTO mode. (The CAST causes the BLOB to lose its table and column name, so AUTO mode doesn't know where to put it.) To simulate this, first create a test table:

 CREATE TABLE Test (One int PRIMARY KEY, Two binary)  INSERT INTO Test VALUES (100, 0xFF) 

Then run the following query:

 SELECT LastName,         CAST(Photo AS image) as Photo  FROM Employees  FOR XML AUTO 

This creates the error in Listing 7.6.

Listing 7.6 Error Generated Trying to CAST a BLOB

 <?xml version="1.0" encoding="utf-8" ?>  <root>  <?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL  Server" Description="FOR XML AUTO could not find the table owning the following  column 'Two' to create a URL address for it.. Remove the column, or use the BINARY  BASE64 mode, or create the URL directly using the  'dbobject/TABLE[@PK1="V1"] /@COLUMN' syntax."?>  </root> 

To fix this, just take out the CAST operator.The result is as follows:

 <?xml version="1.0" encoding="utf-8" ?>  <root>    <Test One="100" Two="dbobject/Test[@One='100']/@Two" />  </root> 

Other Limitations

FOR XML also has other limitations:

• FOR XML cannot be used with cursors .

• As a general rule, FOR XML can only be used in statements that produce direct output to the end user's presentation device (browser).

• You cannot use FOR XML in an INSERT statement inside of a stored procedure.