詳解SQL中FOR XML子句的各種用法

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: ----将元素命名為自定義的名稱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" />

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>