XQUERY - 如何在“value()"函数中使用 sql:variable? [英] XQUERY - How to use the sql:variable in 'value()' function?
本文介绍了XQUERY - 如何在“value()"函数中使用 sql:variable?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
下面的查询试图选择给定节点的子节点.如何使用变量而不是对子节点进行硬编码,以便我可以将它们作为参数传递到 SProc 中?
The query below is trying to select a child node of a given Node. How do I use a variable instead of hard coding the child node such that I can pass them as parameters in a SProc?
declare @T table(XMLCol xml)
insert into @T values
('<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Elem1 type="T1">
<Name type="string" display="First name">John</Name>
<TimeZone display="Time zone">
<children>
<DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
<HiddenName type="string" display="Hidden name">GMT</HiddenName>
</children>
</TimeZone>
</Elem1>
</Root>')
declare @Node varchar(50)
set @Node = 'TimeZone'
select N.value('(children/DisplayName)[1]', 'varchar(100)') as Value
from @T as T
cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N)
推荐答案
declare @T table(XMLCol xml)
insert into @T values
('<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Elem1 type="T1">
<DisplayName type="string" display="Display name">No this</DisplayName>
<Name type="string" display="First name">John</Name>
<TimeZone display="Time zone">
<children>
<DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
<HiddenName type="string" display="Hidden name">GMT</HiddenName>
</children>
</TimeZone>
</Elem1>
</Root>')
declare @Node1 varchar(50)
set @Node1 = 'TimeZone'
declare @Node2 varchar(50)
set @Node2 = 'DisplayName'
select N2.Value.value('.', 'varchar(100)') as Value
from @T as T
cross apply (select T.XMLCol.query('//*[local-name()=sql:variable("@Node1")]')) as N1(Value)
cross apply (select N1.Value.query('//*[local-name()=sql:variable("@Node2")]')) as N2(Value)
这篇关于XQUERY - 如何在“value()"函数中使用 sql:variable?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文