SQL中FOR XML子句的各种用法

FOR XML子句有四种最基本的模式，如上图所示：

1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；
2、RAW模式：返回数据行为<Row>元素，每一列的值作为<Row>元素的属性；
3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
4、EXPLICIT模式：通过SELECT语法定义输出XML的结构

 

具体实例如下：

1、AUTO模式

(1). SQL语句：

   1:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML AUTO, XMLSCHEMA

(2). 所生成的XML文件：

返回XML文件的XML Schema

< xsd:schema  targetNamespace ="urn:schemas-microsoft-com:sql:SqlRowSet1"  xmlns:schema ="urn:schemas-microsoft-com:sql:SqlRowSet1"  xmlns:xsd ="http://www.w3.org/2001/XMLSchema"  xmlns:sqltypes ="http://schemas.microsoft.com/sqlserver/2004/sqltypes"  elementFormDefault ="qualified" >
   < xsd:import  namespace ="http://schemas.microsoft.com/sqlserver/2004/sqltypes"  schemaLocation ="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"   />
   < xsd:element  name ="Employees" >
     < xsd:complexType >
       < xsd:attribute  name ="EmployeeID"  type ="sqltypes:int"  use ="required"   />
       < xsd:attribute  name ="FirstName"  use ="required" >
         < xsd:simpleType >
           < xsd:restriction  base ="sqltypes:nvarchar"  sqltypes:localeId ="1033"  sqltypes:sqlCompareOptions ="IgnoreCase IgnoreKanaType IgnoreWidth"  sqltypes:sqlSortId ="52" >
             < xsd:maxLength  value ="10"   />
           </ xsd:restriction >
         </ xsd:simpleType >
       </ xsd:attribute >
       < xsd:attribute  name ="LastName"  use ="required" >
         < xsd:simpleType >
           < xsd:restriction  base ="sqltypes:nvarchar"  sqltypes:localeId ="1033"  sqltypes:sqlCompareOptions ="IgnoreCase IgnoreKanaType IgnoreWidth"  sqltypes:sqlSortId ="52" >
             < xsd:maxLength  value ="20"   />
           </ xsd:restriction >
         </ xsd:simpleType >
       </ xsd:attribute >
     </ xsd:complexType >
   </ xsd:element >
</ xsd:schema >
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="1"  FirstName ="Nancy"  LastName ="Davolio"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="2"  FirstName ="Andrew"  LastName ="Fuller"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="3"  FirstName ="Janet"  LastName ="Leverling"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="4"  FirstName ="Margaret"  LastName ="Peacock"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="5"  FirstName ="Steven"  LastName ="Buchanan"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="6"  FirstName ="Michael"  LastName ="Suyama"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="7"  FirstName ="Robert"  LastName ="King"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="8"  FirstName ="Laura"  LastName ="Callahan"   />
< Employees  xmlns ="urn:schemas-microsoft-com:sql:SqlRowSet1"  EmployeeID ="9"  FirstName ="Anne"  LastName ="Dodsworth"   />

2、RAW模式

(1). SQL语句：

   1:  ----将元素命名为自定义的名称Employee

   2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')

(2). 所生成的XML文件：

   1:  <Employee EmployeeID="1" FirstName="Nancy" LastName="Davolio" />

   2:  <Employee EmployeeID="2" FirstName="Andrew" LastName="Fuller" />

   3:  <Employee EmployeeID="3" FirstName="Janet" LastName="Leverling" />

   4:  <Employee EmployeeID="4" FirstName="Margaret" LastName="Peacock" />

   5:  <Employee EmployeeID="5" FirstName="Steven" LastName="Buchanan" />

   6:  <Employee EmployeeID="6" FirstName="Michael" LastName="Suyama" />

   7:  <Employee EmployeeID="7" FirstName="Robert" LastName="King" />

   8:  <Employee EmployeeID="8" FirstName="Laura" LastName="Callahan" />

   9:  <Employee EmployeeID="9" FirstName="Anne" LastName="Dodsworth" />

3、PATH模式：

(1). SQL语句：

SELECT EmployeeID "@ID",FirstName  "Name/FirstName",LastName "Name/LastName"

FROM Employees FOR XML PATH ('Employee')

(2). 所生成的XML文件

< Employee  ID ="1" >
   < Name >
     < FirstName > Nancy </ FirstName >
     < LastName > Davolio </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="2" >
   < Name >
     < FirstName > Andrew </ FirstName >
     < LastName > Fuller </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="3" >
   < Name >
     < FirstName > Janet </ FirstName >
     < LastName > Leverling </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="4" >
   < Name >
     < FirstName > Margaret </ FirstName >
     < LastName > Peacock </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="5" >
   < Name >
     < FirstName > Steven </ FirstName >
     < LastName > Buchanan </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="6" >
   < Name >
     < FirstName > Michael </ FirstName >
     < LastName > Suyama </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="7" >
   < Name >
     < FirstName > Robert </ FirstName >
     < LastName > King </ LastName >
   </ Name >
</ Employee >
< Employee  ID ="8" >
   < Name >
     < FirstName > Laura </ FirstName >
     < LastName > Callahan </ LastName >
   </ Name >
</ Employee >

4、EXPLICIT模式

问题：加入要生成如下的XML文档该如何操作？

<Employee EmpID="1">

<FirstName>Nancy</FirstName>

<LastName>Davolio</LastName>

</Employee>

EXPLICIT模式解决这个问题的应用分为两个主要步骤：

1.定义要输出的XML文档结构；

2.传入实际的数据值；

(1). SQL语句：

--定义输出XML文档的数据结构

SELECT  1 AS Tag,

        NULL AS Parent,

        EmployeeID AS [Employee!1!EmpID],

        FirstName AS [Employee!1!FirstName!element],

        LastName AS [Employee!1!LastName!element]

FROM    Employees

UNION ALL

--传入实际的数据

SELECT  1,

        NULL,

        EmployeeID,

        FirstName,

        LastName

FROM    Employees

ORDER BY [Employee!1!EmpID],

        [Employee!1!FirstName!element],

        [Employee!1!LastName!element]

FOR     XML EXPLICIT

语句含义的解释：

先看看定义XML结构的语句输出结果：

Tag栏用来指定生成元素的嵌套水平；1表示嵌套水平为

Parent栏用来指定当前Tag的父级层次；Null值表示该元素为顶级元素；

EmployeeID AS [Employee!1!EmpID],

说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称
FirstName AS [Employee!1!FirstName!element],

说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称！指定值作为元素输出

5、为输出的XML文档添加根元素(Root element)

(1). SQL语句：

SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML AUTO, ROOT('MyRoot')

(2). 所生成的XML文件

< MyRoot >
   < Employees  EmployeeID ="1"  FirstName ="Nancy"  LastName ="Davolio"   />
   < Employees  EmployeeID ="2"  FirstName ="Andrew"  LastName ="Fuller"   />
   < Employees  EmployeeID ="3"  FirstName ="Janet"  LastName ="Leverling"   />
   < Employees  EmployeeID ="4"  FirstName ="Margaret"  LastName ="Peacock"   />
   < Employees  EmployeeID ="5"  FirstName ="Steven"  LastName ="Buchanan"   />
   < Employees  EmployeeID ="6"  FirstName ="Michael"  LastName ="Suyama"   />
   < Employees  EmployeeID ="7"  FirstName ="Robert"  LastName ="King"   />
   < Employees  EmployeeID ="8"  FirstName ="Laura"  LastName ="Callahan"   />
   < Employees  EmployeeID ="9"  FirstName ="Anne"  LastName ="Dodsworth"   />
</ MyRoot >

转载于:https://www.cnblogs.com/zellzhang/archive/2009/05/10/1453836.html