AUTO Mode

for RuBoard

FOR XML AUTO gives you more control than RAW mode over the XML fragment that's produced. To begin with, each row in the result set is named after the table, view, or table-valued UDF that produced it. For example, here's a basic FOR XML AUTO query:

 SELECT CustomerId, CompanyName FROM Customers FOR XML AUTO 

(Results abridged)

 XML_F52E2B61-18A1-11d1-B105-00805F49916B --------------------------------------------------------------------------- <Customers CustomerId="ALFKI" CompanyName="Alfreds Futterkiste"/><>Customers CustomerId="ANATR" CompanyName="Ana Trujillo Emparedados y helados"/><>Customers CustomerId="ANTON" CompanyName="Antonio Moreno Taquer?a"/><Customers  CustomerId="AROUT" CompanyName="Around the Horn"/><>Customers CustomerId="VINET"  CompanyName="Vins et alcools Chevalier"/><Customers CustomerId="WARTH"  CompanyName="Wartian Herkku"/><>Customers CustomerId="WELLI" CompanyName="Wellington Importadora"/><>Customers CustomerId="WHITC"  CompanyName="White Clover Markets"/><>Customers CustomerId="WILMK" CompanyName="Wilman Kala"/><>Customers CustomerId="WOLZA" CompanyName="Wolski Zajazd"/> 

Notice that each row is named after the table from whence it came: Customers. For results with more than one row, this amounts to having more than one top-level (root) element in the fragment, which isn't allowed in XML.

One big difference between AUTO and RAW mode is the way in which joins are handled. In RAW mode, a simple one-to-one translation occurs between columns in the result set and attributes in the XML fragment. Each row becomes an element in the fragment named row. These elements are technically empty themselves . They contain no values or subelements; they only contain attributes. Think of attributes as specifying characteristics of an element, whereas data and subelements compose its contents. In AUTO mode, each row is named after the source from which it came, and the rows from joined tables are nested within one another. Here's an example:

 SELECT Customers.CustomerID, CompanyName, OrderId FROM Customers JOIN Orders ON (Customers.CustomerId=Orders.CustomerId) FOR XML AUTO 

(Results abridged and formatted)

 XML_F52E2B61-18A1-11d1-B105-00805F49916B --------------------------------------------------------------------------- <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">        <Orders OrderId="10643"/><Orders OrderId="10692"/>        <Orders OrderId="10702"/><Orders OrderId="10835"/>        <Orders OrderId="10952"/><Orders OrderId="11011"/> </Customers> <Customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados">        <Orders OrderId="10308"/><Orders OrderId="10625"/>        <Orders OrderId="10759"/><Orders OrderId="10926"/></Customers> <Customers CustomerID="FRANR" CompanyName="France restauration">        <Orders OrderId="10671"/><Orders OrderId="10860"/>        <Orders OrderId="10971"/> </Customers> 

I've formatted the XML fragment to make it easier to read. If you run the query yourself from Query Analyzer, you'll see an unformatted stream of XML text.

Note the way in which the Orders for each customer are contained within each Customer element. As I said, AUTO mode nests the rows returned by joins. Note my use of the full table name in the join criterion. Why didn't I use a table alias? Because AUTO mode uses the table aliases you specify to name the elements it returns. If you use shortened monikers for a table, its elements will have that name in the resulting XML fragment. Although useful in traditional Transact-SQL, this makes the fragment difficult to read if the alias isn't sufficiently descriptive.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net