Using AUTO Mode
AUTO mode returns query results as nested XML elements. Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate attribute of the element. When the ELEMENTS option is specified, the table columns are mapped to subelements instead of attributes. By default, AUTO mode maps the table columns to XML attributes.
A table name (or the alias if provided) maps to the XML element name. A column name (or the alias if provided) maps to an attribute name or noncomplex subelement name when the ELEMENTS option is specified in the query.
The hierarchy (nesting of the elements) in the result set is based on the order of tables identified by the columns specified in the SELECT clause; therefore, the order in which column names are specified in the SELECT clause is significant.
The tables are identified and nested in the order in which the column names are listed in the SELECT clause. The first, leftmost table identified forms the top element in the resulting XML document. The second leftmost table (identified by columns in the SELECT statement) forms a subelement within the top element, and so on.
If a column name listed in the SELECT clause is from a table that is already identified by a previously specified column in the SELECT clause, the column is added as an attribute (or as a subelement if ELEMENTS option is specified) of the element already created, instead of opening a new level of hierarchy (adding a new subelement for that table).
For example, execute this query:
SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO
This is the partial result:
<Customers CustomerID="ALFKI" ContactName="Maria Anders">
<Orders OrderID="10643"/>
<Orders OrderID="10692"/>
<Orders OrderID="10702"/>
<Orders OrderID="10835"/>
<Orders OrderID="10952"/>
<Orders OrderID="11011"/>
</Customers>
Note that in the SELECT clause, CustomerID identifies the Customers table. Therefore, a <Customers> element is created and CustomerID is added as its attribute. Next, the OrderID column name identifies the Orders table. An <Orders> element is added as a subelement of <Customers>, and the OrderID attribute is added to the <Orders> element. Now, the ContactName column identifies the Customers table, which was already identified by the CustomerID column. Therefore, no new element is created. Instead, ContactName attribute is added to the <Customers> element that is already created.
This query specifies the ELEMENT option. Therefore, an element-centric document is returned.
SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, ELEMENTS
This is the partial result:
<Customers>
<CustomerID>ALFKI</CustomerID>
<ContactName>Maria Anders</ContactName>
<Orders><OrderID>10643</OrderID></Orders>
<Orders><OrderID>10692</OrderID></Orders>
<Orders><OrderID>10702</OrderID></Orders>
<Orders><OrderID>10835</OrderID></Orders>
<Orders><OrderID>10952</OrderID></Orders>
<Orders><OrderID>11011</OrderID></Orders>
</Customers>
This query returns employee and order information. Again, the AUTO mode is specified in the FOR XML clause.
SELECT Employees.EmployeeID, LastName, FirstName,
OrderID, OrderDate, Orders.EmployeeID
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
ORDER BY Employees.EmployeeID
FOR XML AUTO
The partial result is shown below. The table name appears as a tag for the XML element in the output. There is one <Employee> element for each value of EmployeeID.
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy">
<Orders OrderID="10258" OrderDate="1996-07-17T00:00:00" EmployeeID="1"/>
<Orders OrderID="10270" OrderDate="1996-08-01T00:00:00" EmployeeID="1"/>
</Employees>
<Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew">
<Orders OrderID="10248" OrderDate="1996-07-04T00:00:00" EmployeeID="5"/>
<Orders OrderID="10249" OrderDate="1996-07-05T00:00:00" EmployeeID="6"/>
</Employees>
Because the Employees table is identified before the Orders table in the SELECT clause, the <Employees> element appears as the outmost element in the resulting hierarchy that contains the <Orders> subelements.
In this example, comparing the EmployeeID values from one row to the next creates the <Employees> elements in the resulting XML document. This is done because EmployeeID is the primary key of the table. If EmployeeID is not identified as the primary key of the Employees table, all the column values from the Employees table specified in the SELECT statement (EmployeeID, LastName, and FirstName) are compared from one row to the next. If any of the values differ from one row to the next, then a new <Employees> element is added in the result.
In comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server™ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
When a column in the SELECT clause cannot be associated with any of the tables identified in the FROM clause (in case of an aggregate column or computed column), the column is added in the XML document in the deepest nesting level in place when it is encountered in the list. If such a column appears as the first column in the SELECT clause, the column is added to the top element.
If the * wildcard character is specified in the SELECT clause, the nesting is determined in the same way as described above (based on the order the rows are returned by the query engine).
The GROUP BY and aggregate functions are not supported in the AUTO mode. However, for a work around in which a nested SELECT is used to retrieve the information, see Example C that follows.
If BINARY BASE64 option is specified in the query, the binary data is returned in base64 encoding format. By default (if BINARY BASE64 option is not specified), the AUTO mode supports URL encoding of binary data. That is, instead of returning the binary data, a reference (a relative URL to the virtual root of the database where query is executed) is returned that can be used to access the actual binary data in subsequent operations. The query must provide enough information such as primary key columns to identify the image.
In a query specified against a table or view, if an alias is specified for the binary column of the view, the alias is returned in the URL encoding of the binary data. In subsequent operations, the alias is meaningless, and the URL encoding cannot be used to retrieve the image. Therefore, do not use aliases when querying a view using FOR XML AUTO mode.
When a view is created using a SELECT statement with TOP n option or DISTINCT option, the primary key information is lost. Therefore, if a query is specified against this view to retrieve a binary column using OPEN XML AUTO mode, an error is returned. For example, if you create the following view:
CREATE VIEW MyView as SELECT TOP 2 * FROM Employees
This query generates an error because MyView does not have EmployeeID as its primary key:
SELECT EmployeeID, Photo
FROM MyView
WHERE EmployeeID = 1
FOR XML AUTO
If the same query is specified against the Employees table, you get these results:
SELECT EmployeeID, Photo
FROM Employees
WHERE EmployeeID = 1
FOR XML AUTO
Examples
The queries in these examples can be executed using SQL Query Analyzer. To execute these queries using HTTP, see Accessing SQL Server Using HTTP.
A. Retrieve employee and order information using the AUTO mode
This query returns employee and order information. AUTO mode is specified in the FOR XML clause:
SELECT Employees.EmployeeID, LastName, FirstName,
OrderID, OrderDate, Orders.EmployeeID
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
ORDER BY Employees.EmployeeID
FOR XML AUTO
The partial result is shown below. The table name appears as a tag for the XML element in the output. There is one <Employee> element for each value of EmployeeID.
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy">
<Orders OrderID="10258" OrderDate="1996-07-17T00:00:00" EmployeeID="1"/>
<Orders OrderID="10270" OrderDate="1996-08-01T00:00:00" EmployeeID="1"/>
</Employees>
<Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew">
<Orders OrderID="10248" OrderDate="1996-07-04T00:00:00" EmployeeID="5"/>
<Orders OrderID="10249" OrderDate="1996-07-05T00:00:00" EmployeeID="6"/>
</Employees>
If the same query is specified in such a way that in the SELECT clause the column from the Orders table is specified before the columns in the Employees table, the hierarchy produced has the <Orders> element as top element and the <Employees> elements at the next level in the hierarchy.
SELECT OrderID, OrderDate, Orders.EmployeeID,
Employees.EmployeeID, LastName, FirstName
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
ORDER BY Employees.EmployeeID
FOR XML AUTO
This is the partial result:
<Orders OrderID="10258" OrderDate="1996-07-17T00:00:00" EmployeeID="1">
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy"/>
</Orders>
<Orders OrderID="10270" OrderDate="1996-08-01T00:00:00" EmployeeID="1">
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy"/>
</Orders>
In the following query, the Orders table is the leftmost table based on the columns specified in the SELECT statement. As a result the <Orders> elements are created as top elements. The columns in the Employees table are specified next in the SELECT statement. The <Employees> element appears nested inside the <Orders> element. Finally, a column in the Orders table is specified in the SELECT statement. However, because the Orders table is already at the top level in the hierarchy, this column is added to that element, and no further elements are created.
SELECT OrderID, Orders.EmployeeID,
Employees.EmployeeID, LastName, FirstName, OrderDate
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
AND Employees.EmployeeID=1 or Employees.EmployeeID=2
ORDER BY Employees.EmployeeID
FOR XML AUTO
This is the partial result:
<Orders OrderID="10258" EmployeeID="1" OrderDate="1996-07-17T00:00:00">
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy"/>
</Orders>
<Orders OrderID="10270" EmployeeID="1" OrderDate="1996-08-01T00:00:00">
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy"/>
</Orders>
B. Specify aliases for table names
This query returns customer and order information. Aliases are used for table names.
SELECT C.CustomerID, O.OrderID, O.OrderDate
FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerID
FOR XML AUTO
The partial result set is shown below. The element names are the same as the aliases specified for the tables used in the query.
<C CustomerID="ALFKI">
<O OrderID="10643" OrderDate="1997-08-25T00:00:00"/>
<O OrderID="10692" OrderDate="1997-10-03T00:00:00"/>
</C>
<C CustomerID="ANATR">
<O OrderID="10308" OrderDate="1996-09-18T00:00:00"/>
</C>
In a nested query, if an alias is specified in the inner query, depending on how the optimizer handles the query, the alias is not preserved. For example:
SELECT TOP 2 *
FROM (SELECT FirstName+' '+LastName as FullName FROM Employees) as EMP
FOR XML AUTO
The query produces this result:
<Employees FullName="Nancy Davolio"/>
<Employees FullName="Andrew Fuller"/>
In the result, the element name is <Employees> instead of <EMP>.
C. Specify GROUP BY and aggregate functions
The GROUP BY and aggregate functions are not currently supported with FOR XML AUTO mode. The following query uses a nested query approach to find the number of orders a customer has placed. This query returns customer information including the number of orders the customer has placed, the order information and the order detail information.
The inner SELECT statement produces a table with customer information along with the number of orders the customer has placed (GROUP BY and COUNT() function are used). This inner table is then joined with tables in FROM clause of the outer query where the FOR XML mode is specified.
SELECT Cust.CustomerID, ContactName, NoOfOrders,
O.OrderID, O.CustomerID,
OD.ProductID, OD.Quantity
FROM (SELECT C.CustomerID, C.ContactName, count(*) as NoOfOrders
FROM Customers C left outer join
Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.ContactName) Cust
left outer join Orders O on Cust.CustomerID = O.CustomerID
left outer join [Order Details] OD on O.OrderID = OD.OrderID
FOR XML AUTO
This is the partial result:
<Cust CustomerID="ALFKI" ContactName="Maria Anders" NoOfOrders="6">
<O OrderID="10643" CustomerID="ALFKI">
<OD ProductID="28" Quantity="15"/>
<OD ProductID="39" Quantity="21"/>
<OD ProductID="46" Quantity="2"/>
</O>
<O OrderID="10692" CustomerID="ALFKI">
<OD ProductID="63" Quantity="20"/>
</O>
</Cust>
D. Specify computed columns in the AUTO mode
This query returns concatenated employee names and the order ID of the orders the employee has taken. The computed column is assigned to the innermost level encountered at that point. The concatenated employee names are added as attributes of <Order> element in the result.
SELECT FirstName+' '+LastName as Name,
Orders.OrderID
FROM Employees left outer join Orders on
Employees.EmployeeID=Orders.EmployeeID
ORDER BY Name
FOR XML AUTO
This is the partial result:
<Orders Name="Andrew Fuller" OrderID="10265"/>
<Orders Name="Andrew Fuller" OrderID="10277"/>
To get the <Emp> elements with Name attribute containing the order subelements, the query is rewritten using a subselect. The innerselect creates a temporary Emp table with the computed column containing the names of the employees. This table is then joined with the Orders table to get the result.
SELECT Emp.name, Orders.OrderID
FROM (SELECT FirstName+' '+LastName as Name,
EmployeeID
FROM Employees) Emp
left outer join Orders on Emp.EmployeeID = Orders.EmployeeID
ORDER BY Emp.Name
FOR XML AUTO
This is the partial result:
<Emp name="Andrew Fuller">
<Orders OrderID="10265"/>
<Orders OrderID="10277"/>
<Orders OrderID="10280"/>
</Emp>
E. Return binary data
This query returns an employee photo from the Employees table. Photo is an image column in the Employees table. The AUTO mode, by default, returns a reference (relative URL to the virtual root of the database where the query is executed) to the binary data. The EmployeeID key attribute must be specified to identify the image. In retrieving an image reference as in this example, the primary key of the table must also be specified in the SELECT clause to uniquely identify a row.
SELECT EmployeeID, Photo
FROM Employees
WHERE EmployeeID=1
FOR XML AUTO
This is the result:
<Employees EmployeeID="1" Photo="dbobject/Employees[@EmployeeID='1']/@Photo"/>
The same query is executed with the BINARY BASE64 option. The query returns the binary data in base64-encoded format.
SELECT Photo
FROM Employees
WHERE EmployeeID=1
FOR XML AUTO, BINARY Base64
This is the result:
<Employees Photo="Here you see the Picture in base64 format"/>
In retrieving binary data using AUTO mode, a reference (a relative URL to the virtual root of the database where the query is executed), instead of the binary data, is returned by default (for example, BINARY BASE64 option is not specified). In case-insensitive databases, if the table or column name specified in the query does not match the table or column name in the database, the query executes; however, the case returned in the reference will not be consistent. For example:
SELECT TOP 2 PHOTO, EMPLOYEEID FROM EMPLOYEES FOR XML AUTO
This is the result:
<EMPLOYEES PHOTO="dbobject/EMPLOYEES[@EmployeeID='1']/@Photo"
EMPLOYEEID="1"/>
<EMPLOYEES PHOTO="dbobject/EMPLOYEES[@EmployeeID='2']/@Photo"
EMPLOYEEID="2"/>
This could be a problem, especially if two templates request data from the same table in a case-insensitive database but use queries with different cases. To avoid such a problem, it is recommended that the case of the table or column name specified in the queries match the case of table or column name in the database.
F. Understand the encoding
This example shows various encoding that takes place in the result.
- Create this table:
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50)) - Add following data to the table:
INSERT INTO [Special Chars] values ('&', 0x20) INSERT INTO [Special Chars] values ('#', 0x20) - This query returns the data from the table. The FOR XML AUTO mode is specified. Binary data is returned as a reference.
SELECT * FROM [Special Chars] FOR XML AUTO
This is the result:
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
This is the process for encoding special characters in the result:
- In the query result, the special XML and URL characters in the element and attribute names returned are encoded using the hexadecimal value of the corresponding Unicode character encoded. In the preceding result, the element name <Special Chars> is returned as <Special_x0020_Chars>. The attribute name <Col#&2> is returned as <Col_x0023__x0026_2> (both XML and URL special characters are encoded).
- If the values of the elements or attribute contain any of the five standard XML character entities (', "", <, >, and &), these special XML characters are always encoded using XML character encoding. In the above result, the value & in the value of attribute <Col1> is encoded as &. However, the # character remains # because it is a valid XML character (not a special XML character).
- If the values of the elements or attributes contain any special URL characters that have special meaning in the URL, they are encoded only in the DBOBJECT URL value and encoded only when the special character is part of a table or column name. In the result, the character # that is part of table name Col#&2 is encoded as _x0023_ in the DBOJBECT URL.
G. Specify the ELEMENTS option
This query returns customer and order information. The query specifies the ELEMENTS option. As a result, the table columns are mapped to subelements.
SELECT Customers.CustomerID, ContactName, OrderID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO, ELEMENTS
This is the result:
<Customers>
<CustomerID>ALFKI</CustomerID>
<ContactName>Maria Anders</ContactName>
<Orders>
<OrderID>10835</OrderID>
<OrderDate>1998-01-15T00:00:00</OrderDate>
</Orders>
<Orders>
<OrderID>10952</OrderID>
<OrderDate>1998-03-16T00:00:00</OrderDate>
</Orders>
</Customers>
In the element-centric mapping, you can specify the same alias for different columns in the query. This results in multiple subelements with the same name (this is not allowed in attribute-centric mapping), for example:
SELECT FirstName name, LastName name
FROM Employees
FOR XML AUTO, EXPLICIT
This is the partial result:
<Employees>
<name>Nancy</name>
<name>Davolio</name>
</Employees>
...