在使用 SQL Server 的 FOR XML AUTO 语句时控制 XML 输出

引言

本文介绍了如何在使用 FOR XML AUTO 语句时更好地控制 XML 输出。例如，当添加 XML 标签时。而不是使用更难理解的 FOR XML EXPLICIT 语句。 如果您即将反序列化 XML 输出以在应用程序中使用，您可能会发现此信息很有用。

背景

在 FOR XML 子句中，您通常使用以下模式之一

• RAW
• AUTO
• EXPLICIT
• PATH

当您想要完全控制生成的 XML 时，您使用 FOR XML EXPLICIT，但这很难理解、阅读和维护最终复杂的 SELECT 语句。 FOR XML AUTO 生成最易读的 SELECT 语句，但缺点是您对生成的 XML 的控制最少。 但通过一些技巧，有时通过使用 PATH 选项，您可以做超出您期望的事情。 RAW 选项很少使用，因此不讨论。 PATH 选项允许您更轻松地混合属性和元素。 现在，我们使用 FOR XML AUTO。

这个例子：

对于本例，我们在一个数据库中使用两个简单的表，它们具有 1:N 的关系。 一个表 (SalesOrder) 包含订单以及客户信息，另一个表 (Items) 包含项目。 一个订单可以有多个项目，一个项目始终属于一个订单。

代码

我总是喜欢从简单的开始。 所以

SELECT * FROM salesorder

产生：

ordernumber customername customerstreet
----------- ------------ --------------
1           parker       first av  
2           lesley       sec av

如果我们要结果集为 XML，我们添加 FOR XML AUTO 语句

SELECT * FROM salesorder FOR XML AUTO

它产生：

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

但是现在，您将字段作为属性。 大部分情况下，您希望它们作为元素。 为此，您添加 ELEMENTS 参数

SELECT * FROM salesorder FOR XML AUTO, ELEMENTS

产生：

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

如果要更改 'salesorder' 标签，您可以使用

SELECT * FROM salesorder AS niceorder FOR XML AUTO, ELEMENTS

生成结果如下

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

当然，这个技巧也适用于列名

SELECT ordernumber AS order_no FROM salesorder WHERE ordernumber = 1 FOR XML AUTO, ELEMENTS

生成结果如下

<salesorder>
    <order_no>1</order_no >
</salesorder>

如果您想添加额外的标签或添加节点怎么办？ 例如，在客户信息周围添加 'customer' 标签？ 这被证明使用 FOR XML AUTO 是困难的。 一个可能的解决方案是 SELF JOIN（连接到同一张表），但我发现了一些更容易的事情。 经过反复试验和修补，我们使用子查询并略微滥用 FOR XML PATH 命令，如下所示

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

生成结果如下

<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>

请注意使用附加的 'TYPE' 参数。 这将确保子查询的结果作为 XML 类型返回（作为整个 XML 类型结果的一部分），而不是 NVARCHAR(MAX) 类型。 如果您想向完整结果添加周围标签，这个巧妙的小技巧也适用

SELECT(
    SELECT 
        customername
    FROM 
        salesorder 
    FOR XML AUTO, TYPE, ELEMENTS
) AS orderrequest FOR XML PATH(''), TYPE, ELEMENTS

生成结果如下

<orderrequest>
    <salesorder>
        <customername>parker</customername>
    </salesorder>
    <salesorder>
        <customername>lesley</customername>
    </salesorder>
</orderrequest>

为什么我们不在子查询中使用标准的 FOR XML AUTO？ 试一试，它会产生一个错误。 只有当子查询是对实际表的查询时，您才能在子查询中使用 FOR XML AUTO（上述情况显然不是）。

如果您想完全控制生成的 XML 输出，子查询结构是正确的。 假设我们希望，每个订单都有客户姓名和属于该订单的所有项目。 为此，您可以使用相关子查询，如下所示

SELECT
    customername ,
    (SELECT    * FROM item WHERE item.ordernumber = 
       salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS)
FROM
    salesorder
FOR XML AUTO, ELEMENTS

生成结果如下

<salesorder>
    <customername>parker</customername>
    <item>
        <itemnumber>10</itemnumber>
        <description>pen</description>
        <ordernumber>1</ordernumber>
    </item>
    <item>
        <itemnumber>11</itemnumber>
        <description>paper</description>
        <ordernumber>1</ordernumber>
    </item>
</salesorder>

当使用相关子查询时，您可以使用常规的 FOR XML AUTO, TYPE, ELEMENTS 语句。 如果您想在 'items' 周围添加标签，只需在子查询后添加 AS，如下所示

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>

为什么我们不简单地将项目表连接到订单表？ 当涉及到生成的 XML 的布局时，这有时会导致不希望的、不可预测的行为。 例如

SELECT
    item.description,
    salesorder.customername
FROM
    salesorder
INNER JOIN item ON item.ordernumber = salesorder.ordernumber
FOR XML AUTO, ELEMENTS

将产生这样的垃圾

<item>
    <description>pen</description>
    <salesorder>
        <customername>parker</customername>
    </salesorder>
</item>
<item>
    <description>paper</description>
    <salesorder>
        <customername>parker</customername>
    </salesorder>
</item>

此外，当使用连接时，您将很难添加周围标签。 因此，对于大多数控制，当使用 FOR XML AUTO 时，请使用（相关）子查询。