在 STUFF/XML 路径中使用 UNION ALL [英] Using UNION ALL in STUFF / XML Path

问题描述

消息 1086，级别 15，状态 1，第 20 行FOR XML 子句在视图、内联函数、派生表和子查询中包含集合运算符时无效.要解决此问题，请使用派生表语法包装包含集合运算符的 SELECT，并在其上应用 FOR XML.

Msg 1086, Level 15, State 1, Line 20 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.

当我运行此程序时出现此错误:

I get this error when i run this:

SELECT
    STUFF((
    SELECT 1
    UNION ALL
    SELECT 2
    FOR XML PATH('')
    ),1,0,'') [COLUMN]

当我运行它时工作正常(没有 Union ALL)

works fine when i run this (without Union ALL)

SELECT
    STUFF((
    SELECT 1
    FOR XML PATH('')
    ),1,0,'') [COLUMN]

有什么建议为什么 UNION ALL 不起作用，或者如何让它在 STUFF() 中工作?

Any suggestions why UNION ALL Doesn't work, or how to get it to work inside the STUFF()?

推荐答案

对此有一个简单的解决方法，您应该使用另一个选择包装联合查询(或任何派生表).这样做然后继续正常的语法:

There's a simple workaround for that, you should wrap your union query(or any derived table for that matter) with another select. Do this and then continue the syntax normally:

select * from
(
SELECT 1 as I
UNION ALL
SELECT 2 as J
) as K

您要搜索的内容是这样的:

Something like this is what you're searching for:

SELECT  STUFF((
    select * from(

    SELECT * from dbo.Table1 as I
    UNION ALL
    SELECT * from dbo.Table2 as j
    ) as k
    FOR XML PATH('')
    ),1,0,'')

我检查过，它完美无缺

这篇关于在 STUFF/XML 路径中使用 UNION ALL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！