SQL FOR XML PATH

SQL Server 中的 FOR XML PATH 模式会返回结果集作为 XML 元素。与其他 XML 模式不同，FOR XML PATH 模式提供了对生成 XML 文件的控制。这是因为 FOR XML PATH 模式将列名和别名视为 XPath 表达式。

在此 SQL FOR XML PATH 模式示例中，我们使用了数据库中的“新员工”和“部门”表。下面的屏幕截图将向您展示“新员工”表的数据。

我们数据库中 SQL Server 部门的数据如下图所示

SQL FOR XML PATH 示例

此示例向您展示了使用 FOR XML PATH 模式的基本方法。使用 PATH 模式的最简单方法是在 SELECT 语句后附加 FOR XML PATH。

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,[DeptID]
  FROM [NewEmployees]
  FOR XML PATH;
SQL FOR XML PATH Example 3

上述查询已生成 XML 文件。请单击超链接以查看 XML 文件。

generated File 4

对于单个表来说是可以的。我将尝试使用 JOIN 在多个表上进行操作。

-- Example

SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH
SQL FOR XML PATH Example 5

它在多个表上都能完美运行

Generated Multiple Files Example 6

SQL FOR XML PATH 示例 2

SQL Server 提供了 ELEMENTS 关键字，可以将列名显示为嵌套元素。我将此关键字与 FOR XML PATH 一起使用。

-- Example

SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH, ELEMENTS;
SQL FOR XML PATH Example 7

看，输出没有区别。因为 PATH 模式已经在执行此操作了

Path Mode 8

SQL FOR XML PATH 示例（带根元素）

如果您观察上面的屏幕截图，每一行都由默认的 元素分隔。在此示例中，我们将向您展示如何用自定义元素覆盖默认的行元素。

-- Example

SELECT Employee.[FirstName] 
      ,Employee.[LastName] 
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees')
SQL FOR XML PATH Example 9

从下面的屏幕截图中， 元素已被 替换

SQL FOR XML PATH 示例 4

FOR XML PATH 允许您创建一个新的根元素来包装所有现有元素。为了实现这一点，我们必须将 ROOT 关键字与 FOR XML PATH 一起使用。

SELECT Employee.[FirstName] 
      ,Employee.[LastName] 
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), ROOT;
SQL FOR XML PATH Example 11

XML 文件是

SQL FOR XML PATH 示例 5

如果您观察上面的屏幕截图，在父级别有一个 元素。在此 for xml path 示例中，我们将更改此默认元素名称。

SELECT Employee.[FirstName] 
      ,Employee.[LastName] 
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), ROOT('EmployeeDetails')
SQL FOR XML PATH Example 13

现在您可以看到 元素已被 替换

SQL FOR XML PATH 示例 6

如果列名（或别名）以 @ 符号开头且不包含 / 符号，则该列将作为属性添加到行元素中。

从下面的 for XML path 代码片段中，“First”和“Last”都以 @ 开头。这意味着它们都被添加为“Employees”行的属性。

SELECT Employee.[FirstName] AS [@First]
      ,Employee.[LastName] AS [@Last]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails');
SQL FOR XML PATH Example 15

XML 文件是

SQL FOR XML PATH 示例 7

如果列名（或别名）不以 @ 符号开头但包含 / 符号，则该列名指示层次结构。

从下面的 for XML path 代码片段中，“First”和“Last”不以 @ 开头，但包含斜杠符号。这意味着创建了一个名为 FullName 的新层次结构，并且“First”和“Last”都作为属性添加到 FullName 中。

SELECT Employee.[FirstName] AS 'FullName/@First'
      ,Employee.[LastName] AS 'FullName/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails');
FOR XML PATH Example 17

XML 文件是

示例 8

您还可以将通配符与 For XML PATH 一起使用。

SELECT Employee.[EmpID] AS [@EmployeeID]
      ,Employee.[FirstName] "*"
      ,Employee.[LastName] "*"
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;
SQL FOR XML PATH Example 23

XML 文件是

View File

FOR XML PATH 示例 9

在此示例中，我们将向您展示 Sql Server XML Path 如何处理未命名列。

-- Example

SELECT Employee.[Education]
      ,SUM(Employee.[YearlyIncome])
  FROM [NewEmployees] AS Employee
  GROUP BY 	Employee.[Education]
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
		  ELEMENTS XSINIL;
SQL FOR XML PATH Example 25

XML 文件是

View File 26

FOR XML PATH 示例 10

如果您观察所有上述示例，生成的 XML 文件会忽略值为 NULL 的元素。这是 For XML Path 的默认行为。但是，您可以添加 ELEMENTS XSINIL 关键字来更改此行为。

SELECT Employee.[FirstName] AS 'Name/@First'
      ,Employee.[LastName] AS 'Name/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;
SQL FOR XML PATH Example 19

现在您可以看到 XML 文件显示了带有 null 值的元素。

XML File With Null Values from Table 20

FOR XML PATH 示例 11

通过将 XMLNAMESPACES 关键字与 SQL For XML Path 一起使用，您可以为 XML 文件分配自定义命名空间。在此示例中，我们将展示这一点。

WITH XMLNAMESPACES('tutorialgateway.org' as TutorialGateway)  
SELECT Employee.[FirstName] AS 'Name/@First'
      ,Employee.[LastName] AS 'Name/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;
FOR XML PATH Example 21

您可以在上面的查询中看到我们使用的命名空间

评论已关闭。