Return SQL Query Result as XML in SQL Server- AUTO Mode

In previous post Return SQL Query Result as XML in SQL Server , I explained For XML clause for getting the query result as XML. I also explained RAW mode of this clause. Today I will explain AUTO  mode in FOR XML  clause.

THE AUTO Mode

The AUTO mode in FOR XML clause is slightly different form  RAW mode in the way of generating XML result set. The  Auto mode generates the XML based on how the  Select  satement is define. To understand this look at examples shown below.

Select Employee.Name,

SalaryInfo.Salary

from EmpName as Employee

Inner Join EmpSalary as SalaryInfo

on Employee.EmpId=SalaryInfo.EmpID

For XML Auto, Root('Employees')

Here I have provides meaningful alias name to the tables(EmpName and EmpSalary). These names are used in defining the XML element names. Now take a look at the resultant XML.

<Employees>

  <Employee Name="Ashish">

    <SalaryInfo Salary="10000" />

  </Employee>

  <Employee Name="Prashant">

    <SalaryInfo Salary="12000" />

  </Employee>

</Employees>

As you can see, <SalaryInfo>  element is a child element of <Employee>. This structure is based on the order of columns, in which you are define them in your Select statement. In the above query, because Name is the first column in the Select  statement so the first element is <Employee> and  remaining column which are associated with EmpSalary table, appear next in Select  statement, they are added as child element.

To understand this lets see an other example. In this query I have changed the order of the columns.

Select SalaryInfo.Salary ,Employee.Name

from EmpName as Employee

Inner Join EmpSalary as SalaryInfo

on Employee.EmpId=SalaryInfo.EmpID

For XML Auto, Root('Employees')

In the above query, you can see that I have changed the order of column in Select statement. Now I have mentioned Salary as first column and Name after that. Now take a look at the resultant XML.

<Employees>

  <SalaryInfo Salary="10000">

    <Employee Name="Ashish" />

  </SalaryInfo>

  <SalaryInfo Salary="12000">

    <Employee Name="Prashant" />

  </SalaryInfo>

</Employees>

Now you can see that <Employee> element is the child of <SalaryInfo>.
In addition, the columns and their values are shown as attributes to the table related elements. This is just like you saw in RAW mode in FOR XML clause. here you can also override this default behaviour by using ELEMENTS  option like  RAW  mode.

Select Employee.Name,

SalaryInfo.Salary

from EmpName as Employee

Inner Join EmpSalary as SalaryInfo

on Employee.EmpId=SalaryInfo.EmpID

For XML Auto, Root('Employees'), Elements

Now take a look at the resultant XML.

<Employees>

  <Employee>

    <Name>Ashish</Name>

    <SalaryInfo>

      <Salary>10000</Salary>

    </SalaryInfo>

  </Employee>

  <Employee>

    <Name>Prashant</Name>

    <SalaryInfo>

      <Salary>12000</Salary>

    </SalaryInfo>

  </Employee>

</Employees>

To be continued...
Happy reading!!