XML查询示例一

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>