详解SQL中FOR XML子句的各种用法（sql for xml）-运维-知优网

本文将讲解SQL中FOR XML子句的各种用法，包括FOR XML字句的四种基本模式：AUTO模式、RAW模式、PATH模式和EXPLICIT模式。

详解SQL中FOR XML子句的各种用法（sql for xml） SQL FOR XML 模式第1张

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

1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；

2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；

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:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>

2、RAW模式

1:  ----将元素命名为自定义的名称Employee2:  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" />   
<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
0
<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
1
<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
2

3、PATH模式：

(1). SQL语句：

<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
3

<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
4

(2). 所生成的XML文件

<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
5

4、EXPLICIT模式

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

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

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

2.传入实际的数据值；

(1). SQL语句：

<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
8

<xsd:schematargetNamespace="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:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
<xsd:elementname="Employees">
<xsd:complexType>
<xsd:attributename="EmployeeID"type="sqltypes:int"use="required"/>
<xsd:attributename="FirstName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="10"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
<xsd:attributename="LastName"use="required">
<xsd:simpleType>
<xsd:restrictionbase="sqltypes:nvarchar"sqltypes:localeId="1033"sqltypes:sqlCompareOptions="IgnoreCaseIgnoreKanaTypeIgnoreWidth"sqltypes:sqlSortId="52">
<xsd:maxLengthvalue="20"/>
xsd:restriction>
xsd:simpleType>
xsd:attribute>
xsd:complexType>
xsd:element>
xsd:schema>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="1"FirstName="Nancy"LastName="Davolio"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="2"FirstName="Andrew"LastName="Fuller"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="3"FirstName="Janet"LastName="Leverling"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="4"FirstName="Margaret"LastName="Peacock"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="5"FirstName="Steven"LastName="Buchanan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="6"FirstName="Michael"LastName="Suyama"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="7"FirstName="Robert"LastName="King"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="8"FirstName="Laura"LastName="Callahan"/>
<Employeesxmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"EmployeeID="9"FirstName="Anne"LastName="Dodsworth"/>
9

1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')0
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')1
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')2
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')3
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')4
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')5
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')6
1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')7

语句含义的解释：

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

详解SQL中FOR XML子句的各种用法（sql for xml） SQL FOR XML 模式第2张

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

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

EmployeeID AS [Employee!1!EmpID],

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

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

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

(1). SQL语句：

1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')8

(2). 所生成的XML文件

1:  ----将元素命名为自定义的名称Employee2:  SELECT EmployeeID,FirstName,LastName FROM Employees FOR XML RAW ('Employee')9

【编辑推荐】
SQL Server置疑数据库解决方法
SQL Server 2005实现数据库缓存依赖
详解在不同的SQL Server数据库之间查询数据