自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

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

作者：阿哲
數(shù)據(jù)庫 SQL Server 數(shù)據(jù)庫運(yùn)維
本文將講解SQL中FOR XML子句的各種用法，包括FOR XML字句的四種基本模式：AUTO模式、RAW模式、PATH模式和EXPLICIT模式。

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

1、AUTO模式：返回?cái)?shù)據(jù)表為起表名的元素，每一列的值返回為屬性；

2、RAW模式：返回?cái)?shù)據(jù)行為元素，每一列的值作為元素的屬性；

3、PATH模式：通過簡單的XPath語法來允許用戶自定義嵌套的XML結(jié)構(gòu)、元素、屬性值

4、EXPLICIT模式：通過SELECT語法定義輸出XML的結(jié)構(gòu)

具體實(shí)例如下：

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" />   
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模式解決這個(gè)問題的應(yīng)用分為兩個(gè)主要步驟

1.定義要輸出的XML文檔結(jié)構(gòu)；

2.傳入實(shí)際的數(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í)際的數(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的父級(jí)層次；Null值表示該元素為***元素； 


EmployeeID AS [Employee!1!EmpID], 


說明：當(dāng)前元素或?qū)傩缘母讣?jí)元素的名稱！元素的標(biāo)簽號(hào)！元素或?qū)傩缘拿Q
FirstName AS [Employee!1!FirstName!element], 


說明：當(dāng)前元素或?qū)傩缘母讣?jí)元素的名稱！元素的標(biāo)簽號(hà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>




【編輯推薦】


    

  1. SQL Server置疑數(shù)據(jù)庫解決方法
    2. 

  2. SQL Server 2005實(shí)現(xiàn)數(shù)據(jù)庫緩存依賴
    3. 

  3. 詳解在不同的SQL Server數(shù)據(jù)庫之間查詢數(shù)據(jù) 


 
責(zé)任編輯：彭凡 
                    來源：
                    cnblogs
 
SQLFOR XML模式
 
 
  
相關(guān)推薦
 
 
 
2019-04-16 15:18:28
 
SQLJOIN數(shù)據(jù)庫
 
 
 
2009-02-23 13:41:42
 
XML操作函數(shù)SQL Server 
 
 
 
2024-07-17 08:15:09
 
 
 
 
2010-09-26 09:50:36
 
SQL Where子句
 
 
 
2010-09-28 11:03:19
 
XML DOM
 
 
 
2011-12-01 10:55:34
 
 
 
 
2023-05-11 16:47:21
 
playwright用法程序
 
 
 
2011-08-22 13:28:56
 
FOR XMLSQL Server
 
 
 
2021-02-07 07:40:31
 
Synchronize鎖用法
 
 
 
2021-12-14 14:50:12
 
synchronize鎖Java
 
 
 
2010-09-26 14:46:19
 
SQL WHERE子句
 
 
 
2010-11-09 10:10:08
 
SQL Server 
 
 
 
2019-03-18 15:00:48
 
SQLJoin用法數(shù)據(jù)庫
 
 
 
2011-08-24 09:15:36
 
SQL Server數(shù)FOR XML AUT
 
 
 
2022-08-03 08:11:58
 
數(shù)據(jù)測(cè)試同類型
 
 
 
2023-10-08 07:37:44
 
SQL連接
 
 
 
2010-03-05 13:28:34
 
SpringObject XML
 
 
 
2010-09-08 15:42:46
 
SQL指令UNION
 
 
 
2010-09-10 15:04:41
 
SQLDATENAME函數(shù)
 
 
 
2021-08-16 08:12:04
 
SQLMerge用法
 
  
點(diǎn)贊 
 
收藏 
 
  
  
 
51CTO技術(shù)棧公眾號(hào)