65.9K
CodeProject 正在变化。 阅读更多。
Home

FOR XML 基础 (EXPLICIT 模式)：3/4

AhsanAhmed
starIconstarIconstarIconstarIconstarIcon

5.00/5 (4投票s)

2017年6月2日

CPOL

5分钟阅读

viewsIcon

20717

简要介绍如何在 Microsoft SQL Server 中使用 EXPLICIT 模式的 FOR XML 子句将数据以 XML 格式返回。

引言

很久以前，我接到一个任务，需要通过存储过程从数据库生成 XML 文件，并给我提供了一个存储过程作为参考。参考存储过程将数据提取到游标中，然后处理每一条记录，通过追加字符串字面量来创建 XML 元素，最后返回生成的“XML”字符串。看了那个存储过程后，我心想肯定有更好的方法，事实也确实如此。使用 FOR XML 子句，可以将表记录作为 XML 数据返回。学习之后，我决定写一篇文章来介绍它。

文章系列路线图

这是 4 部分系列文章中的第 3 篇。其他文章列表如下

Content

背景

需要对 SQL JOIN 和 SET 运算符有基本了解才能继续学习。

Using the Code

下面的文章将使用以下数据库。您可以复制/粘贴以下代码以继续学习，或者创建自己的数据库并调整查询。

创建数据库，如下所示

CREATE DATABASE FOR_XML_TUTORIAL;

执行以下语句来创建 CUSTOMER 和 ORDER 表并填充数据。

USE [FOR_XML_TUTORIAL];

CREATE TABLE [CUSTOMER]
(
    CUSTOMER_ID INT PRIMARY KEY NOT NULL,
    FIRST_NAME VARCHAR(25) NOT NULL,
    LAST_NAME VARCHAR(25) NOT NULL,
    POSTAL_CODE VARCHAR(2) NOT NULL,
);

CREATE TABLE [ORDER]
(
    ORDER_ID INT PRIMARY KEY NOT NULL,
    CUSTOMER_ID INT NOT NULL REFERENCES CUSTOMER(CUSTOMER_ID),
    TOTAL_ITEMS INT NOT NULL,
    TOTAL_AMOUNT NUMERIC(18,2) NOT NULL
);

INSERT INTO CUSTOMER VALUES (1, 'John', 'Michaels', 'TX');

INSERT INTO CUSTOMER VALUES (2, 'Shawn', 'Cena', 'MA');

INSERT INTO CUSTOMER VALUES (3, 'Dwayne', 'Austin', 'TX');

INSERT INTO CUSTOMER VALUES (4, 'Steve', 'Johnson', 'FL');

INSERT INTO [ORDER] VALUES (1, 1, 5, 32.50);

INSERT INTO [ORDER] VALUES (2, 1, 2, 21.36);

INSERT INTO [ORDER] VALUES (3, 2, 7, 59.00);

INSERT INTO [ORDER] VALUES (4, 3, 2, 18.24);

INSERT INTO [ORDER] VALUES (5, 4, 3, 30.00);

INSERT INTO [ORDER] VALUES (6, 4, 6, 66.00);

FOR XML 模式

使用 FOR XML 子句时，必须指定一种模式，该模式会相应地返回 XML。以下是可用模式列表

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

EXPLICIT 模式

在 RAW 和 AUTO 模式下，我们对 XML 的创建控制力很小。SQL Server 提供了另外两种模式，我们可以通过它们更精细地控制 XML 的创建，其中一种就是我们将在本教程中学习的 EXPLICIT 模式，另一种 PATH 模式将在下一篇教程中介绍。

使用 EXPLICIT 模式，我们可以控制 XML 的创建，例如定义层级结构，以及定义值是显示为属性还是单独的元素，并提供更多选项。但是，更高的控制力也伴随着更高的复杂性。要使用 EXPLICIT 模式，我们必须创建能够表示 XML 层级结构的 SQL SELECT 语句。此外，我们必须为层级中的每个级别创建一个单独的 SELECT 语句，并使用 UNION ALL 连接记录集。理解 EXPLICIT 模式的最佳方式是通过示例，让我们来看一个。

我们将使用上面提到的表来输出以下 XML。

<Customer CustomerID="1">
  <FirstName>John</FirstName>
  <LastName>Michaels</LastName>
  <PostalCode>TX</PostalCode>
  <Order OrderID="1">
    <Items>5</Items>
    <Amount>32.50</Amount>
  </Order>
  <Order OrderID="2">
    <Items>2</Items>
    <Amount>21.36</Amount>
  </Order>
</Customer>
<Customer CustomerID="2">
  <FirstName>Shawn</FirstName>
  <LastName>Cena</LastName>
  <PostalCode>MA</PostalCode>
  <Order OrderID="3">
    <Items>7</Items>
    <Amount>59.00</Amount>
  </Order>
</Customer>
<Customer CustomerID="3">
  <FirstName>Dwayne</FirstName>
  <LastName>Austin</LastName>
  <PostalCode>TX</PostalCode>
  <Order OrderID="4">
    <Items>2</Items>
    <Amount>18.24</Amount>
  </Order>
</Customer>
<Customer CustomerID="4">
  <FirstName>Steve</FirstName>
  <LastName>Johnson</LastName>
  <PostalCode>FL</PostalCode>
  <Order OrderID="5">
    <Items>3</Items>
    <Amount>30.00</Amount>
  </Order>
  <Order OrderID="6">
    <Items>6</Items>
    <Amount>66.00</Amount>
  </Order>
</Customer>

我认为，先编写能返回某种程度上类似于最终 XML 数据的记录集的 SELECT 语句，然后在我对结果集满意后，再将其转换为 XML，这样更容易。我们将以同样的方式解决这个问题。

在上面的 XML 中，层级的第一个级别包含客户信息。如前所述，层级的每个级别都将映射到一个 SELECT 语句，所以我们来创建一个用于客户信息的。

SELECT      C.CUSTOMER_ID,
            C.FIRST_NAME,
            C.LAST_NAME,
            C.POSTAL_CODE,
            NULL AS ORDER_ID,
            NULL AS    TOTAL_ITEMS,
            NULL AS TOTAL_AMOUNT
FROM        [CUSTOMER] C

上面的查询是一个简单的 SELECT 语句，没有什么复杂的，除了选取的 ORDER_ID、TOTAL_ITEMS 和 TOTAL_AMOUNT 这三列值为 NULL。我稍后会解释为什么包含这些列。
返回的记录集是

接下来，我们将为层级的下一个级别——客户订单信息——创建 SELECT 语句。以下是查询。

SELECT        C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID

上面的查询返回

现在，我们将使用 UNION ALL 子句连接这两个记录集。现在您将看到为什么在第一个查询中包含 3 个 NULL 列的原因，因为要对记录集执行集合操作，列数必须相等。
以下是查询。

SELECT        C.CUSTOMER_ID,
              C.FIRST_NAME,
              C.LAST_NAME,
              C.POSTAL_CODE,
              NULL AS ORDER_ID,
              NULL AS    TOTAL_ITEMS,
              NULL AS TOTAL_AMOUNT
FROM          [CUSTOMER] C
UNION ALL
SELECT        C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID

上面的查询返回

现在，让我们添加一个 ORDER BY 子句，以确保我们的记录集能代表 XML 结构。按 CUSTOMER_ID 然后按 ORDER_ID 排序，这样 NULL ORDER_ID 的记录就会排在前面。

SELECT        C.CUSTOMER_ID,
              C.FIRST_NAME,
              C.LAST_NAME,
              C.POSTAL_CODE,
              NULL AS ORDER_ID,
              NULL AS    TOTAL_ITEMS,
              NULL AS TOTAL_AMOUNT
FROM          [CUSTOMER] C
UNION ALL
SELECT        C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY      CUSTOMER_ID, ORDER_ID

上面的查询返回

现在，这个记录集代表了我们的最终 XML 数据。让我们使用 EXPLICIT 模式将其转换为 XML。

要将此记录集转换为 XML，我们必须在前面两个位置包含 2 个新列

TAG：TAG 数字代表层级的深度。
PARENT：层级中当前级别的父级的 TAG 数字。

SELECT        1 AS TAG,
              NULL AS PARENT,
              C.CUSTOMER_ID,
              C.FIRST_NAME,
              C.LAST_NAME,
              C.POSTAL_CODE,
              NULL AS ORDER_ID,
              NULL AS    TOTAL_ITEMS,
              NULL AS TOTAL_AMOUNT
FROM          [CUSTOMER] C
UNION ALL
SELECT        2 AS TAG,
              1 AS PARENT,
              C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY      CUSTOMER_ID, ORDER_ID

正如您在第一个 SELECT 语句中所见，我们选择 TAG 为 1，PARENT 为 NULL，因为它是层级中的第一个元素，没有父级。类似地，在第二个 SELECT 语句中，我们选择 TAG 为 2，PARENT 为 1，因为它位于层级的第二级，其父级是层级中 TAG 为 1 的元素。
此外，我们必须为第一个 SELECT 语句中的每个列提供一个非常复杂的结构化别名，如下所示：

<ELEMENT>!<TAG>!<ATTRIBUTE>[!<DIRECTIVE>]

其中，

<ELEMENT>：值将被赋给的元素的名称。
<TAG>：表示层级或深度的标签号。
<ATTRIBUTE>：值将被赋给的属性的名称。
<DIRECTIVE>：这是可选的，用于提供 XML 创建的额外信息。我们将介绍它的一个选项“ELEMENT”。

回到我们的示例，现在我们的 SELECT 查询看起来像

SELECT        1 AS TAG,
              NULL AS PARENT,
              C.CUSTOMER_ID AS [Customer!1!CustomerID],
              C.FIRST_NAME AS [Customer!1!FirstName!ELEMENT],
              C.LAST_NAME AS [Customer!1!LastName!ELEMENT],
              C.POSTAL_CODE AS [Customer!1!PostalCode!ELEMENT],
              NULL AS [Order!2!OrderID],
              NULL AS    [Order!2!Items!ELEMENT],
              NULL AS [Order!2!Amount!ELEMENT]
FROM          [CUSTOMER] C
UNION ALL
SELECT        2 AS TAG,
              1 AS PARENT,
              C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY      CUSTOMER_ID, ORDER_ID

让我们来看第一个别名 [Customer!1!CustomerID]
这里，
Customer 表示值将被赋给的元素。
1 表示层级级别或深度。
CustomerID 表示值将被赋给的属性。

如果指定了可选的 DIRECTIVE ELEMENT，则会创建一个名为 CustomerID 的子元素，而不是像 C.FIRST_NAME 那样的属性。

让我们修改 ORDER BY 子句以包含新的别名。

SELECT        1 AS TAG,
              NULL AS PARENT,
              C.CUSTOMER_ID AS [Customer!1!CustomerID],
              C.FIRST_NAME AS [Customer!1!FirstName!ELEMENT],
              C.LAST_NAME AS [Customer!1!LastName!ELEMENT],
              C.POSTAL_CODE AS [Customer!1!PostalCode!ELEMENT],
              NULL AS [Order!2!OrderID],
              NULL AS    [Order!2!Items!ELEMENT],
              NULL AS [Order!2!Amount!ELEMENT]
FROM          [CUSTOMER] C
UNION ALL
SELECT        2 AS TAG,
              1 AS PARENT,
              C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY      [Customer!1!CustomerID], [Order!2!OrderID]

最后，在上面的查询中附加 FOR XML EXPLICIT。

SELECT        1 AS TAG,
              NULL AS PARENT,
              C.CUSTOMER_ID AS [Customer!1!CustomerID],
              C.FIRST_NAME AS [Customer!1!FirstName!ELEMENT],
              C.LAST_NAME AS [Customer!1!LastName!ELEMENT],
              C.POSTAL_CODE AS [Customer!1!PostalCode!ELEMENT],
              NULL AS [Order!2!OrderID],
              NULL AS    [Order!2!Items!ELEMENT],
              NULL AS [Order!2!Amount!ELEMENT]
FROM          [CUSTOMER] C
UNION ALL
SELECT        2 AS TAG,
              1 AS PARENT,
              C.CUSTOMER_ID,
              C.FIRST_NAME,            
              C.LAST_NAME,
              C.POSTAL_CODE,
              O.ORDER_ID,
              O.TOTAL_ITEMS,
              O.TOTAL_AMOUNT
FROM          [CUSTOMER] C
INNER JOIN    [ORDER] O
ON            C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY      [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT

就这么简单。以下是上述查询的结果。

<Customer CustomerID="1">
  <FirstName>John</FirstName>
  <LastName>Michaels</LastName>
  <PostalCode>TX</PostalCode>
  <Order OrderID="1">
    <Items>5</Items>
    <Amount>32.50</Amount>
  </Order>
  <Order OrderID="2">
    <Items>2</Items>
    <Amount>21.36</Amount>
  </Order>
</Customer>
<Customer CustomerID="2">
  <FirstName>Shawn</FirstName>
  <LastName>Cena</LastName>
  <PostalCode>MA</PostalCode>
  <Order OrderID="3">
    <Items>7</Items>
    <Amount>59.00</Amount>
  </Order>
</Customer>
<Customer CustomerID="3">
  <FirstName>Dwayne</FirstName>
  <LastName>Austin</LastName>
  <PostalCode>TX</PostalCode>
  <Order OrderID="4">
    <Items>2</Items>
    <Amount>18.24</Amount>
  </Order>
</Customer>
<Customer CustomerID="4">
  <FirstName>Steve</FirstName>
  <LastName>Johnson</LastName>
  <PostalCode>FL</PostalCode>
  <Order OrderID="5">
    <Items>3</Items>
    <Amount>30.00</Amount>
  </Order>
  <Order OrderID="6">
    <Items>6</Items>
    <Amount>66.00</Amount>
  </Order>
</Customer>

总结

以上就是 FOR XML 子句中的 EXPLICIT 模式的所有内容。要了解更多关于 FOR XML 的信息，您可以访问 MSDN SQL Server 联机丛书。在下一篇文章中，我们将学习PATH模式。

© . All rights reserved.