XML查询示例一

1,创建XML， 使用 FOR XML AUTO

USE AdventureWorks2012SELECT BusinessEntityID,    PersonType,    Title,    FirstName,    MiddleName,    LastName,    SuffixFROM Person.PersonWHERE BusinessEntityID = 100FOR XML AUTOGO

<Person.Person BusinessEntityID="100" PersonType="EM" FirstName="Lolan" MiddleName="B" LastName="Song" />

2,创建XML，使用 FOR XML AUTO ELEMENTS

SELECT BusinessEntityID,    PersonType,    Title,    FirstName,    MiddleName,    LastName,    SuffixFROM Person.PersonWHERE BusinessEntityID = 100FOR XML AUTO,ELEMENTSGO

<Person.Person>
<BusinessEntityID>100</BusinessEntityID>
<PersonType>EM</PersonType>
<FirstName>Lolan</FirstName>
<MiddleName>B</MiddleName>
<LastName>Song</LastName>
</Person.Person>

3，创建XML，使用 FOR XML PATH

SELECT BusinessEntityID,    PersonType,    Title,    FirstName,    MiddleName,    LastName,    SuffixFROM Person.PersonWHERE BusinessEntityID = 100FOR XML PATH('PERSON')GO

<PERSON>
<BusinessEntityID>100</BusinessEntityID>
<PersonType>EM</PersonType>
<FirstName>Lolan</FirstName>
<MiddleName>B</MiddleName>
<LastName>Song</LastName>
</PERSON>

4，指定节点属性

SELECT BusinessEntityID AS '@ID',    PersonType,    Title,    FirstName,    MiddleName,    LastName,    SuffixFROM Person.PersonWHERE BusinessEntityID = 100FOR XML PATH('Person')GO 

<Person ID="100">
<PersonType>EM</PersonType>
<FirstName>Lolan</FirstName>
<MiddleName>B</MiddleName>
<LastName>Song</LastName>
</Person>

5，包含XML列

SELECT BusinessEntityID AS '@ID',    PersonType,    Title,    FirstName,    MiddleName,    LastName,    Suffix,   DemographicsFROM Person.PersonWHERE BusinessEntityID = 10000FOR XML PATH('Person')GO 

<Person ID="10000">
<PersonType>IN</PersonType>
<FirstName>Haley</FirstName>
<MiddleName>C</MiddleName>
<LastName>Mitchell</LastName>
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>2457.33</TotalPurchaseYTD>
<DateFirstPurchase>2003-12-11Z</DateFirstPurchase>
<BirthDate>1966-08-24Z</BirthDate>
<MaritalStatus>S</MaritalStatus>
<YearlyIncome>greater than 100000</YearlyIncome>
<Gender>F</Gender>
<TotalChildren>0</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Graduate Degree</Education>
<Occupation>Management</Occupation>
<HomeOwnerFlag>0</HomeOwnerFlag>
<NumberCarsOwned>4</NumberCarsOwned>
<CommuteDistance>1-2 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person>