Use AUTO Mode with FOR XML. 4 minutes to read.In this articleAPPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data WarehouseAs described in, AUTO mode returns query results as nested XML elements. This does not provide much control over the shape of the XML generated from a query result.
A clear, useful description of what's allowed in an XML element (or other) name can be found in section 2.3, 'Common Syntactic Constructs.' A Name is a token beginning with a letter or one of a few punctuation characters, and continuing with letters, digits, hyphens, underscores, colons, or full stops, together known as name characters.
The AUTO mode queries are useful if you want to generate simple hierarchies. However, and provide more control and flexibility in deciding the shape of the XML from a query result.Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element.
![Not Not](/uploads/1/2/5/4/125424953/701019649.png)
The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified in the FOR XML clause.The XML hierarchy, nesting of the elements, in the resulting XML 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 first, leftmost table that is 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 of the element already created, instead of opening a new level of hierarchy.
If the ELEMENTS option is specified, the column is added as an attribute.For example, execute this query: SELECT Cust.CustomerID,OrderHeader.CustomerID,OrderHeader.SalesOrderID,OrderHeader.Status,Cust.CustomerTypeFROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeaderWHERE Cust.CustomerID = OrderHeader.CustomerIDORDER BY Cust.CustomerIDFOR XML AUTOThis is the partial result.Note the following in the SELECT clause:.The CustomerID references the Cust table. Therefore, a element is created and CustomerID is added as its attribute.Next, three columns, OrderHeader.CustomerID, OrderHeader.SaleOrderID, and OrderHeader.Status, reference the OrderHeader table.
Therefore, an element is added as a subelement of the element and the three columns are added as attributes of.Next, the Cust.CustomerType column again references the Cust table that was already identified by the Cust.CustomerID column. Therefore, no new element is created. Instead, the CustomerType attribute is added to the element that was previously created.The query specifies aliases for the table names.
These aliases appear as corresponding element names.ORDER BY is required to group all children under one parent.This query is similar to the previous one, except the SELECT clause specifies columns in the OrderHeader table before the columns in the Cust table. Therefore, first element is created and then the child element is added to it. Select OrderHeader.CustomerID,OrderHeader.SalesOrderID,OrderHeader.Status,Cust.CustomerID,Cust.CustomerTypefrom Sales.Customer Cust, Sales.SalesOrderHeader OrderHeaderwhere Cust.CustomerID = OrderHeader.CustomerIDfor xml autoThis is the partial result.If the ELEMENTS option is added in the FOR XML clause, element-centric XML is returned. SELECT Cust.CustomerID,OrderHeader.CustomerID,OrderHeader.SalesOrderID,OrderHeader.Status,Cust.CustomerTypeFROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeaderWHERE Cust.CustomerID = OrderHeader.CustomerIDORDER BY Cust.CustomerIDFOR XML AUTO, ELEMENTSThis is the partial result: 1S1438605.In this query, the CustomerID values are compared from one row to the next in creating the elements, because CustomerID is the primary key of the table. If CustomerID is not identified as the primary key for the table, all the column values (CustomerID, CustomerType in this query) are compared from one row to the next.
If the values differ, a new element is added to the XML.When comparing these column values, if any of the columns to be compared are of type text, ntext, image, or xml, FOR XML assumes that the values are different and not compared, even though they may be the same. This is because comparing large objects is not supported.
![Which Which](/uploads/1/2/5/4/125424953/497868595.gif)
Elements are added to the result for each row selected. Note that columns of (n)varchar(max) and varbinary(max) are compared.When a column in the SELECT clause cannot be associated with any of the tables identified in the FROM clause, as in the 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 asterisk (.) wildcard character is specified in the SELECT clause, the nesting is determined in the same way as previously described, based on the order that the rows are returned by the query engine. In This SectionThe following topics provide more information about AUTO mode:.See Also.