1,创建XML, 使用 FOR XML AUTO
USE AdventureWorks2012
SELECT BusinessEntityID,
PersonType,
Title,
FirstName,
MiddleName,
LastName,
Suffix
FROM Person.Person
WHERE BusinessEntityID = 100
FOR XML AUTO
GO
<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,
Suffix
FROM Person.Person
WHERE BusinessEntityID = 100
FOR XML AUTO,ELEMENTS
GO
<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,
Suffix
FROM Person.Person
WHERE BusinessEntityID = 100
FOR 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,
Suffix
FROM Person.Person
WHERE BusinessEntityID = 100
FOR 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,
Demographics
FROM Person.Person
WHERE BusinessEntityID = 10000
FOR 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>