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

FOR XML 基础 (AUTO 模式)：2/4

AhsanAhmed
starIconstarIconstarIconstarIconstarIcon

5.00/5 (8投票s)

2017年5月29日

CPOL

3分钟阅读

viewsIcon

12122

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

引言

很久以前，我接到一个任务，要求使用存储过程从数据库生成 XML 文件，并且给我提供了一个存储过程作为参考。该参考存储过程将数据提取到游标中，然后处理每条记录，通过附加字符串字面量来创建 XML 元素，然后返回生成的字符串 "XML"。 在查看了那个存储过程之后，我对自己说，一定有更好的方法来做到这一点，而且确实有。使用FOR XML子句，可以将表记录作为 XML 数据返回。 在了解了它之后，我决定写一篇关于它的文章。

文章系列路线图

这是 4 部分系列文章中的第 2 篇。其他文章列在下面

Content

背景

要理解本文，需要对 SQL 连接有基本的了解。

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

AUTO 模式

当所需的 XML 包含多层嵌套时，在 FOR XML 子句中使用 AUTO 模式。

我们将使用以下查询来返回客户信息以及订单以供演示。

SELECT        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

该查询返回

要在 AUTO 模式下返回 XML 数据，请将 FOR XML AUTO 附加到上述查询中。

SELECT        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
FOR XML AUTO

上述查询返回

正如您所看到的，返回的 XML 包含两种元素类型 C 和 O。 这些元素的命名是由于在查询中为表名提供了别名。 另请注意 XML 中的元素具有父子关系。 父或外层元素是 C，包含客户信息作为属性/值对，子或内层元素是 O，包含订单信息作为属性/值对。 这种关系排序由 select 查询中的第一列确定。 在上述查询中，第一列来自 Customer 表，因此 Customer 成为 父 元素。 要反转此顺序，只需将 Orders 表中的任何一列放在 select 子句的第一个位置，如下所示

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

上述查询返回

如上所述，现在包含 订单 信息的 O 元素是 父 元素，包含 客户 信息的 C 元素是 子 元素。

要更改元素和属性名称，只需在上述查询中提供有意义的别名即可。

SELECT        Customers.FIRST_NAME,            
              Customers.LAST_NAME,
              Customers.POSTAL_CODE,
              Orders.ORDER_ID,
              Orders.TOTAL_ITEMS,
              Orders.TOTAL_AMOUNT
FROM          [CUSTOMER] Customers
INNER JOIN    [ORDER] Orders
ON            Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO

上述查询返回

要将返回的 XML 嵌套在 root 元素内，请将 ROOT 关键字与所需的 root 元素名称一起附加在括号中，如下所示

SELECT        Customers.FIRST_NAME,            
              Customers.LAST_NAME,
              Customers.POSTAL_CODE,
              Orders.ORDER_ID,
              Orders.TOTAL_ITEMS,
              Orders.TOTAL_AMOUNT
FROM          [CUSTOMER] Customers
INNER JOIN    [ORDER] Orders
ON            Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO, ROOT('CustomerList')

上述查询返回

到目前为止，我们执行的所有查询都以一种格式返回 XML 数据，其中每一列都转换为一个属性。 要更改此格式并返回 XML，其中每一列都映射到其自己的 element，请附加 ELEMENTS 关键字，如下所示

SELECT        Customers.FIRST_NAME,            
              Customers.LAST_NAME,
              Customers.POSTAL_CODE,
              Orders.ORDER_ID,
              Orders.TOTAL_ITEMS,
              Orders.TOTAL_AMOUNT
FROM          [CUSTOMER] Customers
INNER JOIN    [ORDER] Orders
ON            Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO, ROOT('CustomerList'), ELEMENTS

现在，每条记录的属性都转换成一个元素，其名称设置为其别名，其值设置为查询返回的值。

注意：我减少了行数以使下面的图像更小。

总结

这就是 FOR XML 子句中的 AUTO 模式。 在下一篇文章中，我们将学习 EXPLICIT 模式。

© . All rights reserved.