問題描述
我在創(chuàng)建 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'))
這個查詢工作正常，但是當我嘗試像這里這樣執(zhí)行 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:
數(shù)據(jù)類型 xml 不能用作 UNION、INTERSECT 的操作數(shù)或 EXCEPT 運算符，因為它沒有可比性.
The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
推薦答案
您可能對此感興趣:
請比較以下內(nèi)容
測試"一詞出現(xiàn)在兩個列表中.UNION 會隱式地做一個 DISTINCT，所以test"只出現(xiàn)一次.
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"出現(xiàn)兩次
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
命名空間重復創(chuàng)建，沒有錯，但是很煩(見這個: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');
這篇關于創(chuàng)建 XML 文件時在 SQL 中聯(lián)合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！