在SQL函数中使用FOR XML时如何删除嵌套查询中的冗余名称空间 [英] How do I remove redundant namespace in nested query when using FOR XML in SQL Function
问题描述
There is a solution for remove namespace here!, but I need it works in SQL function, I'm getting this error:
当FOR XML子句包含set运算符时，它们在视图，内联函数，派生表和子查询中无效.要解决此问题，请使用派生表语法包装包含set运算符的SELECT并在其之上应用FOR XML.
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
有人可以帮我吗?
感谢Xtyan
来自评论:这是所需的XML输出
From comment: This is the needed XML-output
<zoo xmlns:an="uri:animal">
<an:animal species="Mouse">
<an:legs>
<an:leg>Front Right</an:leg>
<an:leg>Front Left</an:leg>
<an:leg>Back Right</an:leg>
<an:leg>Back Left</an:leg>
</an:legs>
</an:animal>
<an:animal species="Chicken">
<an:legs>
<an:leg>Right</an:leg>
<an:leg>Left</an:leg>
</an:legs>
</an:animal>
<an:animal species="Snake" />
</zoo>
推荐答案
您可能不会喜欢这个……
You will not like this probably...
我没有找到通用的解决方案...用重复的名称空间创建它是没有问题的.这没有错，但很烦人:
I did not find a generic solution for this... It is no problem to create this with repeated namespaces. This is not wrong, but annoying:
declare @xml as xml;
WITH XMLNAMESPACES('uri:animal' as an)
select @xml = (
select
a.c2 as "@an:species"
, (
select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('an:leg'), type
) as "an:legs"
from t1 a
for xml path('an:animal'),root('zoo'));
一种解决方案:通过字符串连接构建
但这太丑了...
One solution: build this via string-concatenation
But this is super-ugly...
以下解决方案使用FLWOR在XML创建后重新创建XML，但这不是通用的.必须将此名称空间的一个元素添加到<zoo>，否则，该名称空间将在更深的级别上重复创建.我添加了属性an:Dummy.
The following solution uses FLWOR to re-create the XML after its creation, but this is not generic. It is necessary to add one element of this namespaces to the <zoo>, otherwise the namespace is created on a deeper level repeatedly. I added the attribut an:Dummy.
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');
GO
--the function
CREATE FUNCTION dbo.TestFunc()
RETURNS XML
AS
BEGIN
declare @xml as xml;
WITH XMLNAMESPACES('uri:animal' as an)
select @xml = (
select
a.c2 as "@an:species"
, (
select l.c3 as "text()"
from t2 l where l.c2 = a.c1
for xml path('an:leg'), type
) as "an:legs"
from t1 a
for xml path('an:animal'));
set @xml=@xml.query('declare namespace an="uri:animal";
<zoo an:Dummy="dummy">
{
for $a in /an:animal
return <an:animal an:species="{$a/@an:species}"><an:legs>
{
for $l in $a/an:legs/an:leg
return <an:leg>{$l/text()}</an:leg>
}
</an:legs></an:animal>
}
</zoo>');
return @xml;
END
GO
--the call
SELECT dbo.TestFunc();
GO
--clean up
drop function dbo.TestFunc;
drop table t1
drop table t2
结果
<zoo xmlns:an="uri:animal" an:Dummy="dummy">
<an:animal an:species="Mouse">
<an:legs>
<an:leg>Front Right</an:leg>
<an:leg>Front Left</an:leg>
<an:leg>Back Right</an:leg>
<an:leg>Back Left</an:leg>
</an:legs>
</an:animal>
<an:animal an:species="Chicken">
<an:legs>
<an:leg>Right</an:leg>
<an:leg>Left</an:leg>
</an:legs>
</an:animal>
<an:animal an:species="Snake">
<an:legs />
</an:animal>
</zoo>
上一个答案
好的，我想我第一次尝试时就完全错了.下面是一个示例，其中一个函数返回不添加名称空间的XML.
Previous answer
Okay, I think I got this completely wrong in my first attempt. The following is an example, where a function returns the XML without added namespaces.
我使用后面的答案之一，该答案预先构建内部XML而没有名称空间，并创建完整的XML作为具有名称空间的第二次调用.请检查一下:
I use one of the later answers which builds the inner XML in advance without a namespace and creates the full XML as a second call with a namespace. Please check this out:
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');
GO
-功能
CREATE FUNCTION dbo.TestFunc()
RETURNS XML
AS
BEGIN
declare @xml as xml;
select @xml = (
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'));
declare @resultXML XML;
;with XmlNamespaces( 'uri:animal' as an)
select @ResultXML= (SELECT @xml for xml path('') , root('zoo'),TYPE);
return @resultXML;
END
GO
-通话
SELECT dbo.TestFunc();
GO
-清理
drop function dbo.TestFunc;
drop table t1
drop table t2
结果
<zoo xmlns:an="uri:animal">
<animal species="Mouse">
<legs>
<leg>Front Right</leg>
<leg>Front Left</leg>
<leg>Back Right</leg>
<leg>Back Left</leg>
</legs>
</animal>
<animal species="Chicken">
<legs>
<leg>Right</leg>
<leg>Left</leg>
</legs>
</animal>
<animal species="Snake" />
</zoo>
这篇关于在SQL函数中使用FOR XML时如何删除嵌套查询中的冗余名称空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
