使用 FOR XML PATH 时如何删除嵌套查询中的冗余命名空间 [英] How do I remove redundant namespace in nested query when using FOR XML PATH
问题描述
更新:我发现针对此问题提出了一个 Microsoft Connect 项目这里
UPDATE: I've discovered there is a Microsoft Connect item raised for this issue here
当使用 FOR XML PATH
和 WITH XMLNAMESPACES
声明默认命名空间时,对于使用 FOR XML 的嵌套查询,我将在任何顶级节点中复制命名空间声明,我在网上偶然发现了一些解决方案,但我并不完全相信......
When using FOR XML PATH
and WITH XMLNAMESPACES
to declare a default namespace, I will get the namespace declaration duplicated in any top level nodes for nested queries that use FOR XML, I've stumbled across a few solutions on-line, but I'm not totally convinced...
这是一个完整的例子
/*
drop table t1
drop table t2
*/
create table t1 ( c1 int, c2 varchar(50))
create table t2 ( c1 int, c2 int, c3 varchar(50))
insert t1 values
(1, 'Mouse'),
(2, 'Chicken'),
(3, 'Snake');
insert t2 values
(1, 1, 'Front Right'),
(2, 1, 'Front Left'),
(3, 1, 'Back Right'),
(4, 1, 'Back Left'),
(5, 2, 'Right'),
(6, 2, 'Left')
;with XmlNamespaces( default 'uri:animal')
select
a.c2 as "@species"
, (select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('leg'), type) as "legs"
from t1 a
for xml path('animal'), root('zoo')
最好的解决方案是什么?
What's the best solution?
推荐答案
如果我理解正确的话,您指的是您可能在这样的查询中看到的行为:
If I have understood correctly, you are referring to the behavior that you might see in a query like this:
DECLARE @Order TABLE (
OrderID INT,
OrderDate DATETIME)
DECLARE @OrderDetail TABLE (
OrderID INT,
ItemID VARCHAR(1),
ItemName VARCHAR(50),
Qty INT)
INSERT @Order
VALUES
(1, '2010-01-01'),
(2, '2010-01-02')
INSERT @OrderDetail
VALUES
(1, 'A', 'Drink', 5),
(1, 'B', 'Cup', 2),
(2, 'A', 'Drink', 2),
(2, 'C', 'Straw', 1),
(2, 'D', 'Napkin', 1)
;WITH XMLNAMESPACES('http://test.com/order' AS od)
SELECT
OrderID AS "@OrderID",
(SELECT
ItemID AS "@od:ItemID",
ItemName AS "data()"
FROM @OrderDetail
WHERE OrderID = o.OrderID
FOR XML PATH ('od.Item'), TYPE)
FROM @Order o
FOR XML PATH ('od.Order'), TYPE, ROOT('xml')
结果如下:
<xml xmlns:od="http://test.com/order">
<od.Order OrderID="1">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="B">Cup</od.Item>
</od.Order>
<od.Order OrderID="2">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="C">Straw</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="D">Napkin</od.Item>
</od.Order>
</xml>
如您所说,命名空间在子查询的结果中重复.
As you said, the namespace is repeated in the results of the subqueries.
根据 devnetnewsgroup(网站现已解散)上的对话,此行为是一项功能,尽管可以选择 投票改变它.
This behavior is a feature according to a conversation on devnetnewsgroup (website now defunct) although there is the option to vote on changing it.
我建议的解决方案是恢复到 FOR XML EXPLICIT
:
My proposed solution is to revert back to FOR XML EXPLICIT
:
SELECT
1 AS Tag,
NULL AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
NULL AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL AS [od:Item!3!ItemID]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
OrderID AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL [od:Item!3!ItemID]
FROM @Order
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
o.OrderID AS [od:Order!2!OrderID],
d.ItemName AS [od:Item!3],
d.ItemID AS [od:Item!3!ItemID]
FROM @Order o INNER JOIN @OrderDetail d ON o.OrderID = d.OrderID
ORDER BY [od:Order!2!OrderID], [od:Item!3!ItemID]
FOR XML EXPLICIT
并查看这些结果:
<xml xmlns:od="http://test.com/order">
<od:Order OrderID="1">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="B">Cup</od:Item>
</od:Order>
<od:Order OrderID="2">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="C">Straw</od:Item>
<od:Item ItemID="D">Napkin</od:Item>
</od:Order>
</xml>
这篇关于使用 FOR XML PATH 时如何删除嵌套查询中的冗余命名空间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!