SQL XML 输出控制

在数据库查询中,将数据输出为XML格式是一种常见的需求。SQL Server 提供了几种方式来生成XML,其中 FOR XML AUTO 是最简单也是最常用的一种方式。它能够自动将查询结果转换为XML格式,但同时也牺牲了对XML结构的精细控制。本文将探讨如何使用 FOR XML AUTO 以及如何通过一些技巧来增强其灵活性。

SQLServer中,FOR XML 子句提供了四种模式来生成XML:RAW、AUTO、EXPLICIT和PATH。其中:

  • RAW:提供最基本的XML输出,但使用起来较为复杂。
  • AUTO:自动生成XML,易于理解和维护,但控制力较弱。
  • EXPLICIT:提供完全的控制,但语法复杂,难以阅读和维护。
  • PATH:允许更灵活地混合使用属性和元素。

本文主要讨论 FOR XML AUTO 的使用及其扩展技巧。

简单示例

假设有两个简单的表:一个是订单表(SalesOrder),包含订单和客户信息;另一个是商品表(Items),包含订单中的商品。一个订单可以包含多个商品,而一个商品只能属于一个订单。

首先,来看一个简单的查询:

SELECT * FROM salesorder

这将返回订单号、客户名和客户街道等信息。如果希望将结果集转换为XML,可以添加 FOR XML AUTO 子句:

SELECT * FROM salesorder FOR XML AUTO

这将生成如下XML:

<salesorder ordernumber="1" customername="parker" customerstreet="first av" /> <salesorder ordernumber="2" customername="lesley" customerstreet="sec av" />

默认情况下,字段会被转换为XML属性。如果希望它们成为元素,可以添加 ELEMENTS 参数:

SELECT * FROM salesorder FOR XML AUTO, ELEMENTS

生成的XML将如下所示:

<salesorder> <ordernumber>1</ordernumber> <customername>parker</customername> <customerstreet>first av</customerstreet> </salesorder> <salesorder> <ordernumber>2</ordernumber> <customername>lesley</customername> <customerstreet>sec av</customerstreet> </salesorder>

如果想要自定义标签名,可以使用别名:

SELECT * FROM salesorder AS niceorder FOR XML AUTO, ELEMENTS

生成的XML将使用自定义的标签名:

<niceorder> <ordernumber>1</ordernumber> <customername>parker</customername> <customerstreet>first av</customerstreet> </niceorder> <niceorder> <ordernumber>2</ordernumber> <customername>lesley</customername> <customerstreet>sec av</customerstreet> </niceorder>

如果想要在客户信息周围添加额外的标签,如 'customer' 标签,可以使用子查询和 FOR XML PATH

SELECT ordernumber, ( SELECT customername, customerstreet FOR XML PATH(''), TYPE, ELEMENTS ) AS customer FROM salesorder FOR XML AUTO, ELEMENTS

生成的XML将包含额外的 'customer' 标签:

<salesorder> <ordernumber>1</ordernumber> <customer> <customername>parker</customername> <customerstreet>first av</customerstreet> </customer> </salesorder> <salesorder> <ordernumber>2</ordernumber> <customer> <customername>lesley</customername> <customerstreet>sec av</customerstreet> </customer> </salesorder>

高级技巧

如果想要对生成的XML输出有完全的控制,可以使用子查询。例如,如果想要为每个订单添加客户名和所有属于该订单的商品:

SELECT customername, ( SELECT * FROM item WHERE item.ordernumber = salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS ) AS orderitems FROM salesorder FOR XML AUTO, ELEMENTS <salesorder> <customername>parker</customername> <orderitems> <item> <itemnumber>10</itemnumber> <description>pen</description> <ordernumber>1</ordernumber> </item> <item> <itemnumber>11</itemnumber> <description>paper</description> <ordernumber>1</ordernumber> </item> </orderitems> </salesorder>
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485