创建 XML 文件时在 SQL 中联合

问题描述

我在创建 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 运算符，因为它没有可比性.

推荐答案

您可能对此感兴趣:

请比较以下内容

测试"一词出现在两个列表中.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');

这篇关于创建 XML 文件时在 SQL 中联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！