Query FOR XML Clause(SQL Server 2012) single table

官方语法太过复杂，Example也太过冗余

[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
    {
      { RAW [ ('ElementName') ] | AUTO }
        [
           <CommonDirectives>
           [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
           [ , ELEMENTS [ XSINIL | ABSENT ]
        ]
      | EXPLICIT
        [
           <CommonDirectives>
           [ , XMLDATA ]
        ]
      | PATH [ ('ElementName') ]
        [
           <CommonDirectives>
           [ , ELEMENTS [ XSINIL | ABSENT ] ]
        ]
     }

<CommonDirectives> ::=
   [ , BINARY BASE64 ]
   [ , TYPE ]
   [ , ROOT [ ('RootName') ] ]

Basic Syntax of the FOR XML Clause

 

SELECT TOP 2 persontype, FirstName, LastName
FROM AdventureWorks2012.Person.Person
for BROWSE

  

SELECT TOP 2 persontype, FirstName, LastName
FROM AdventureWorks2012.Person.Person
for XML RAW

<row persontype="EM" FirstName="Ken" LastName="Sánchez" />
<row persontype="EM" FirstName="Terri" LastName="Duffy" />

 

SELECT TOP 2 persontype, FirstName, LastName
FROM AdventureWorks2012.Person.Person
for XML AUTO

<AdventureWorks2012.Person.Person persontype="EM" FirstName="Ken" LastName="Sánchez" />
<AdventureWorks2012.Person.Person persontype="EM" FirstName="Terri" LastName="Duffy" />

 

<AdventureWorks2012.Person.Person>
  <persontype>EM</persontype>
  <FirstName>Ken</FirstName>
  <LastName>Sánchez</LastName>
</AdventureWorks2012.Person.Person>
<AdventureWorks2012.Person.Person>
  <persontype>EM</persontype>
  <FirstName>Terri</FirstName>
  <LastName>Duffy</LastName>
</AdventureWorks2012.Person.Person>

SELECT TOP 2 persontype, FirstName, LastName
FROM AdventureWorks2012.Person.Person
for XML AUTO,elements ABSENT

 

<row>
  <persontype>EM</persontype>
  <FirstName>Ken</FirstName>
  <LastName>Sánchez</LastName>
</row>
<row>
  <persontype>EM</persontype>
  <FirstName>Terri</FirstName>
  <LastName>Duffy</LastName>
</row>

SELECT TOP 2 persontype, FirstName, LastName
FROM AdventureWorks2012.Person.Person
for XML RAW,elements ABSENT 

 

<persontype persontype="EM">
  <FirstName>Ken</FirstName>
  <LastName>Sánchez</LastName>
</persontype>
<persontype persontype="EM">
  <FirstName>Terri</FirstName>
  <LastName>Duffy</LastName>
</persontype>

SELECT TOP 2 persontype as '@persontype', FirstName, LastName
FROM AdventureWorks2012.Person.Person
for XML path('persontype')