XML查询示例一

来源：互联网发布：做网络销售需要会什么编辑：程序博客网时间：2024/06/05 15:19

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>