FOR XML AUTO模式详解

数据库操作中,有时需要将查询结果以XML格式返回。SQLServer提供了一种名为FOR XML的子句,它允许以XML格式返回查询结果。本文是关于FOR XML子句的系列文章的第二篇,将深入探讨AUTO模式的使用。

本系列文章共有四篇,分别介绍了FOR XML的四种模式:RAW、AUTO、EXPLICIT和PATH。本文是系列的第二篇,专注于AUTO模式。

要理解本文内容,需要具备基本的SQLJOIN知识。

使用示例

为了演示AUTO模式,将使用一个示例数据库。可以复制以下代码创建数据库并插入数据,或者创建自己的数据库并调整查询。

CREATE DATABASE FOR_XML_TUTORIAL; 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 ORDERS ( 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 ORDERS VALUES (1, 1, 5, 32.50); INSERT INTO ORDERS VALUES (2, 1, 2, 21.36); INSERT INTO ORDERS VALUES (3, 2, 7, 59.00); INSERT INTO ORDERS VALUES (4, 3, 2, 18.24); INSERT INTO ORDERS VALUES (5, 4, 3, 30.00); INSERT INTO ORDERS VALUES (6, 4, 6, 66.00);

FOR XML模式

在使用FOR XML子句时,必须指定一种模式,以返回相应的XML数据。以下是可用模式的列表:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

AUTO模式

FOR XML子句中的AUTO模式用于生成包含多级嵌套的XML。将使用以下查询返回客户信息及其订单作为演示。

SELECT C.FIRST_NAME, C.LAST_NAME, C.POSTAL_CODE, O.ORDER_ID, O.TOTAL_ITEMS, O.TOTAL_AMOUNT FROM CUSTOMER C INNER JOIN ORDERS 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 ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID FOR XML AUTO

上述查询返回的XML包含两种元素类型C和O。这些元素之所以这样命名,是因为在查询中为表名提供了别名。同时注意XML中的元素具有父子关系。父元素或外层元素是C,包含客户信息作为属性/值对,子元素或内层元素是O,包含订单信息作为属性/值对。这种关系顺序由SELECT查询中的第一列决定。在上面的查询中,第一列来自Customer表,因此Customer成为父元素。要反转这个顺序,只需在SELECT子句中将任何来自Orders表的列放在第一位,如下所示:

SELECT O.ORDER_ID, C.FIRST_NAME, C.LAST_NAME, C.POSTAL_CODE, O.TOTAL_ITEMS, O.TOTAL_AMOUNT FROM CUSTOMER C INNER JOIN ORDERS 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 ORDERS Orders ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID FOR XML AUTO

要将返回的XML嵌套在根元素内,添加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 ORDERS Orders ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID FOR XML AUTO, ROOT('CustomerList')

到目前为止,执行的所有查询都返回了XML数据,其中每列都转换为一个属性。要更改此格式并返回每列映射到其自己的元素的XML,添加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 ORDERS Orders ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID FOR XML AUTO, ROOT('CustomerList'), ELEMENTS
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485