FOR XML Auto

In our previous article, we learned about retrieving data in XML format using FOR XML RAW. In this article we will see how we can do the same using Auto mode and how it differs from RAW Mode. For Auto can provide us better ways to display and present nested data. We will start by looking at some examples with For Auto mode to be clearer. Similar to our last article we will be using our AdventureWorks Database.

Below is a simple query

select pc.ProductCategoryID, p.Name,Weight

from SalesLT.Product p

inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

order by pc.ProductCategoryID

for xml Auto

<pc ProductCategoryID="5">

<p Name="Mountain-100 Silver, 38" Weight="9230.56" />

<p Name="Mountain-100 Silver, 42" Weight="9421.06" />

<p Name="Mountain-100 Silver, 44" Weight="9584.36" />

</pc>

……

Now here we see how Name and Weight are nested inside ProductCategoryID giving a clean hierarchy of details that belongs to that particular ProductCategoryID. We can specify the ROOT tag similar to RAW Mode but we cannot have any row tag with For Auto Clause , so For Auto('Product') is not allowed and would return an error message. However, if we wish to change the tag <pc> and <p> in above example we can do as below

select "Pcategory".ProductCategoryID as pid,"Pcategory".Name, "Product".Name,Weight

from SalesLT.Product "Product"

inner join SalesLT.ProductCategory "Pcategory"

ON "Product".ProductCategoryID ="Pcategory".ProductCategoryID

order by "Pcategory".ProductCategoryID

for xml Auto, ROOT('Product')

 

<Product>

<Pcategory pid="5" Name="Mountain Bikes">

<Product Name="Mountain-100 Silver, 38" Weight="9230.56" />

<Product Name="Mountain-100 Silver, 42" Weight="9421.06" />

…..

</Pcategory>

</Product>

If we want to convert attributes to Elements we can easily do that by specifying ELEMENTS clause next to Auto Clause and for Null values we can specify XSINIL with element clause just as we did in RAW as shown below:

select "Pcategory".ProductCategoryID as pid,"Pcategory".Name, "Product".Name,Weight

from SalesLT.Product "Product"

inner join SalesLT.ProductCategory "Pcategory"

ON "Product".ProductCategoryID ="Pcategory".ProductCategoryID

order by "Pcategory".ProductCategoryID

for xml Auto,ELEMENTS XSINIL, ROOT('Product')

Once of the things to note about displaying LOB in XML Auto is we can specify Binary Base 64 as we did for RAW as below

 

select "Pcategory".ProductCategoryID as pid,"Pcategory".Name, "Product".Name,"Product".ThumbNailPhoto

from SalesLT.Product "Product"

inner join SalesLT.ProductCategory "Pcategory"

ON "Product".ProductCategoryID ="Pcategory".ProductCategoryID

order by "Pcategory".ProductCategoryID

for xml Auto,ELEMENTS XSINIL, ROOT('Product'),Binary Base64

But with AUTO mode we can select binary data even without specifying Binary Base 64, provided we select primary key value of the table from which we are selecting binary data. See example below, in this, in order to select "Product".ThumbNailPhoto without Binary Base 64 we have to select "Product".ProductID which is the primary key :

 

select "Pcategory".ProductCategoryID as pid,"Pcategory".Name, "Product".Name,"Product".ProductID

,"Product".ThumbNailPhoto

from SalesLT.Product "Product"

inner join SalesLT.ProductCategory "Pcategory"

ON "Product".ProductCategoryID ="Pcategory".ProductCategoryID

order by "Pcategory".ProductCategoryID

for xml Auto,ELEMENTS XSINIL, ROOT('Product')

The reason for Primary key to be present is because SQL Server needs to generate the path to the binary column.

Below query is neither specifying Binary Base64 nor the primary key and hence will return an error message as "FOR XML AUTO requires primary keys to create references for 'ThumbNailPhoto'. Select primary keys, or use BINARY BASE64 to obtain binary data in encoded form if no primary keys exist.

 

select "Pcategory".ProductCategoryID as pid,"Pcategory".Name, "Product".Name--,"Product".ProductID

,"Product".ThumbNailPhoto

from SalesLT.Product "Product"

inner join SalesLT.ProductCategory "Pcategory"

ON "Product".ProductCategoryID ="Pcategory".ProductCategoryID

order by "Pcategory".ProductCategoryID

for xml Auto,ELEMENTS XSINIL, ROOT('Product')

So, from what we have learned there are some points to keep in mind

Let's start by doing some comparisons between FOR XML RAW and AUTO to be clearer

  1. AUTO gives a nested format but there can be only one path. Multiple paths won't work.
  2. You can have binary data type without specifying Binary Base 64 if you have included Primary key of the table containing binary column in select statement
  3. You can change the nesting Path by changing the order of the column in select statement
  4. XMLSCHEMA and XMLData clause are also applicable and work just as FOR XML RAW mode

Tags:

SQL Server | XML