詳解SQL中FOR XML子句的各種用法
作者：阿哲
本文將講解SQL中FOR XML子句的各種用法，包括FOR XML字句的四種基本模式：AUTO模式、RAW模式、PATH模式和EXPLICIT模式。
FOR XML子句有四種最基本的模式，如上圖所示：
1、AUTO模式：返回數(shù)據(jù)表為起表名的元素，每一列的值返回為屬性；
2、RAW模式：返回數(shù)據(jù)行為
3、PATH模式：通過簡單的XPath語法來允許用戶自定義嵌套的XML結(jié)構(gòu)、元素、屬性值
4、EXPLICIT模式：通過SELECT語法定義輸出XML的結(jié)構(gòu)
具體實例如下：
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: ----將元素命名為自定義的名稱Employee |
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模式解決這個問題的應(yīng)用分為兩個主要步驟
1.定義要輸出的XML文檔結(jié)構(gòu)；
2.傳入實際的數(shù)據(jù)值；
(1). SQL語句：
--定義輸出XML文檔的數(shù)據(jù)結(jié)構(gòu)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--傳入實際的數(shù)據(jù)SELECT 1, NULL, EmployeeID, FirstName, LastNameFROM EmployeesORDER BY [Employee!1!EmpID], [Employee!1!FirstName!element], [Employee!1!LastName!element] FOR XML EXPLICIT |
語句含義的解釋：
先看看定義XML結(jié)構(gòu)的語句輸出結(jié)果：
Tag欄用來指定生成元素的嵌套水平；1表示嵌套水平為
Parent欄用來指定當(dāng)前Tag的父級層次；Null值表示該元素為***元素；
EmployeeID AS [Employee!1!EmpID],
說明：當(dāng)前元素或?qū)傩缘母讣壴氐拿Q！元素的標(biāo)簽號！元素或?qū)傩缘拿Q
FirstName AS [Employee!1!FirstName!element],
說明：當(dāng)前元素或?qū)傩缘母讣壴氐拿Q！元素的標(biāo)簽號！元素或?qū)傩缘拿Q！指定值作為元素輸出
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>
【編輯推薦】
責(zé)任編輯：彭凡
來源：
cnblogs
