创建 XML 文件时在 SQL 中联合

2021-10-01 00:00:00 xml sql sql-server

我在创建 XML 文件的 SQL 查询中遇到了一些问题.我想做 UNION 它这个查询，但它不起作用.

I got some problem with my SQL query which create a XML file. I want to do UNION it this query but it doesn't work.

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))

这个查询工作正常，但是当我尝试像这里这样执行 UNION 时:

This query works fine but when I try to do UNION like here:

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)

UNION

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL2].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))

这个查询给我一个错误:

This query give me an error:

数据类型 xml 不能用作 UNION、INTERSECT 的操作数或 EXCEPT 运算符，因为它没有可比性.

The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

推荐答案

您可能对此感兴趣:

请比较以下内容

测试"一词出现在两个列表中.UNION 会隐式地做一个 DISTINCT，所以test"只出现一次.

The word "test" occurs in both lists. UNION will do a DISTINCT implicitly, so "test" appears only once.

SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words);

与UNION ALL一样会让test"出现两次

The same with UNION ALL will let the "test" appear twice

SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION ALL
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words);

您可以将 UNION SELECT 放入周围的 SELECT(UNION 或 UNION ALL 并设置FOR XML PATH 用于整个结果集

You can put your UNION SELECT into a surrounding SELECT (either UNION or UNION ALL and set the FOR XML PATH for the whole result-set

命名空间重复创建，没有错，但是很烦(见这个:https://stackoverflow.com/a/35648751/5089204 和链接的连接文章)

The namespace is created repeatedly, not wrong, but annoying (see this: https://stackoverflow.com/a/35648751/5089204 and the linked Connect-Article)

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT *
FROM
(
    SELECT * 
    FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
    UNION
    SELECT * 
    FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
) AS MetaTable
FOR XML Path(''),ROOT('UNION_TEST');

这将带回两个列表，每个列表都在它自己的 XML 标签中，还有重复的命名空间(见之前)

This will bring back both lists, each in its own XML tag, also repeated namespace (see before)

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT
 (
    SELECT * 
    FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
    FOR XML PATH(''),ROOT('FirstBlock'),TYPE
 )
,(
    SELECT * 
    FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
    FOR XML PATH(''),ROOT('FirstBlock'),TYPE
 )
FOR XML Path(''),ROOT('UNION_TEST');

最后你也可以使用它(无论是否使用ALL):

And finally you can use this too (either with ALL or not):

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION ALL
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
FOR XML PATH(''),ROOT('UNION_TEST');

相关文章