SQL UNION FOR XML 名称输出列 [英] SQL UNION FOR XML name output column

问题描述

我正在尝试从 SQL 生成 XML 输出，需要使用 UNION 语句并命名输出列.

I'm trying to generate an XML output from SQL and need to use a UNION statement and also name the output column.

当我不需要使用 UNION 语句时，我以前使用过:

I had this working before when I didn't need to use a UNION statement using:

select(
SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAccs] FOR XML PATH ('AccountDetails'), root ('Root') 
) as XmlOutput

将输出 XML 列命名为 XmlOutput

Which named the output XML column as XmlOutput

我正在尝试:

select(
SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAccs]

UNION

SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAppAccs]



 FOR XML PATH ('AccountDetails'), root ('Root')
) as XmlOutput

但是收到一条错误消息，有人知道解决方法吗?

But receive an error message, does anyone know a way around this?

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

谢谢J.

推荐答案

像这样将您的 2 个选择包裹在一个:

Wrap your 2 selects on a single one like so:

select (
    select id, name from (
        select id, name 
        from xmltest 
        UNION
        select id, name 
        from xmltest 
    ) A
    FOR XML PATH ('AccountDetails'), root ('Root')
) As XmlOutput

这篇关于SQL UNION FOR XML 名称输出列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！