本文将讲解SQL中FOR XML子句的各种用法,包括FOR XML字句的四种基本模式:AUTO模式、RAW模式、PATH模式和EXPLICIT模式。
AD:
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: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: ----将元素命名为自定义的名称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" />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>NancyFirstName> <LastName>DavolioLastName> Name> Employee> <Employee ID="2"> <Name> <FirstName>AndrewFirstName> <LastName>FullerLastName> Name> Employee> <Employee ID="3"> <Name> <FirstName>JanetFirstName> <LastName>LeverlingLastName> Name> Employee> <Employee ID="4"> <Name> <FirstName>MargaretFirstName> <LastName>PeacockLastName> Name> Employee> <Employee ID="5"> <Name> <FirstName>StevenFirstName> <LastName>BuchananLastName> Name> Employee> <Employee ID="6"> <Name> <FirstName>MichaelFirstName> <LastName>SuyamaLastName> Name> Employee> <Employee ID="7"> <Name> <FirstName>RobertFirstName> <LastName>KingLastName> Name> Employee> <Employee ID="8"> <Name> <FirstName>LauraFirstName> <LastName>CallahanLastName> Name> Employee>
4、EXPLICIT模式
问题:加入要生成如下的XML文档该如何操作?
Nancy Davolio 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, LastNameFROM EmployeesORDER 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>
【编辑推荐】