FOR Clause in Transact-SQL

This clause is very import in responding when in XML format.
http://msdn.microsoft.com/ja-jp/library/ms173812.aspx

The default return of
Select * From table_name For XML AUTO
is very ugly.

e.g,

Select * From dbo.Staff FOR XML AUTO

returns

<dbo.Staff Name="Bill" Age="10" Gender="M" Occupation="Children" Company="Home" />
<dbo.Staff Name="Bob" Age="23" Gender="M" Occupation="Driver" Company="Driver" />
<dbo.Staff Name="Jack" Age="30" Gender="M" Occupation="Doctor" Company="Microsoft" />
<dbo.Staff Name="Jim" Age="53" Gender="M" Occupation="Sniper" Company="SWAT" />
<dbo.Staff Name="Merry" Age="24" Gender="F" Occupation="Waitress" Company="DotClub" />
<dbo.Staff Name="Nathan" Age="33" Gender="M" Occupation="Driver" Company="Sagawa" />
<dbo.Staff Name="Shelly" Age="35" Gender="F" Occupation="Actress" Company="NHK" />
<dbo.Staff Name="Tracy" Age="30" Gender="F" Occupation="Accountant" Company="PG" />

Select * From dbo.Staff FOR XML PATH, ROOT('Staff')

returns

<Staff>
  <row>
    <Name>Bill</Name>
    <Age>10</Age>
    <Gender>M</Gender>
    <Occupation>Children</Occupation>
    <Company>Home</Company>
  </row>
  <row>
    <Name>Bob</Name>
    <Age>23</Age>
    <Gender>M</Gender>
    <Occupation>Driver</Occupation>
    <Company>Driver</Company>
  </row>
  <row>
    <Name>Jack</Name>
    <Age>30</Age>
    <Gender>M</Gender>
    <Occupation>Doctor</Occupation>
    <Company>Microsoft</Company>
  </row>
  <row>
    <Name>Jim</Name>
    <Age>53</Age>
    <Gender>M</Gender>
    <Occupation>Sniper</Occupation>
    <Company>SWAT</Company>
  </row>
  <row>
    <Name>Merry</Name>
    <Age>24</Age>
    <Gender>F</Gender>
    <Occupation>Waitress</Occupation>
    <Company>DotClub</Company>
  </row>
  <row>
    <Name>Nathan</Name>
    <Age>33</Age>
    <Gender>M</Gender>
    <Occupation>Driver</Occupation>
    <Company>Sagawa</Company>
  </row>
  <row>
    <Name>Shelly</Name>
    <Age>35</Age>
    <Gender>F</Gender>
    <Occupation>Actress</Occupation>
    <Company>NHK</Company>
  </row>
  <row>
    <Name>Tracy</Name>
    <Age>30</Age>
    <Gender>F</Gender>
    <Occupation>Accountant</Occupation>
    <Company>PG</Company>
  </row>
</Staff>

which can be readed like this

using System;
using System.Data.SqlClient;
using System.Xml;
 
namespace DataBaseDemoReadToXML
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var conn =
                new SqlConnection(connection_string);
            conn.Open();
            var command = new SqlCommand("Select * From dbo.Staff FOR XML PATH, ROOT('Staff')", conn);
            XmlReader reader = command.ExecuteXmlReader();
            reader.Read();
            Console.WriteLine(reader.ReadOuterXml());
            conn.Close();
 
            Console.ReadLine();
        }
    }
}