問題描述

我在創建 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');

這篇關于創建 XML 文件時在 SQL 中聯合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！