获取 SQL XML 值的 XPath [英] XPath to fetch SQL XML value
问题描述
这是我的问题:从列中的以下 XML 中,我想知道名称为已启用"的变量的值是否等于给定步骤 ID 和组件 ID 的是".
Here is my problem: from the following XML that is within a column, I want to know if the value of a variable with the name 'Enabled' is equal to 'Yes' given a step Id and a component Id.
'<xml>
<box stepId="1">
<components>
<component id="2">
<variables>
<variable id="3" nom="Server" valeur="DEV1" />
<variable id="4" nom="Enabled" valeur="Yes" />
</variables>
</component>
<component id="3">
<variables>
<variable id="3" nom="Server" valeur="DEV1" />
<variable id="4" nom="Enabled" valeur="No" />
</variables>
</component>
</components>
</box>
<box stepId="2">
<components>
<component id="2">
<variables>
<variable id="3" nom="Server" valeur="DEV2" />
<variable id="4" nom="Enabled" valeur="Yes" />
</variables>
</component>
<component id="3">
<variables>
<variable id="3" nom="Server" valeur="DEV2" />
<variable id="4" nom="Enabled" valeur="No" />
</variables>
</component>
</components>
</box>
</xml>'
推荐答案
- XQuery 针对 xml 数据类型
- 一般 XQuery 用例
- 涉及层次结构的 XQuery
-
更新
我的建议是以面向集合的方式,而不是在 XML 中搜索特定节点的过程方式,将 XML 分解为关系,并对结果关系进行搜索和连接.这是一个简单的 XML 查询,用于切碎感兴趣的节点和属性:
My recomendation would be to shred the XML into relations and do searches and joins on the resulted relation, in a set oriented fashion, rather than the procedural fashion of searching specific nodes in the XML. Here is a simple XML query that shreds out the nodes and attributes of interest:
select x.value(N'../../../../@stepId', N'int') as StepID , x.value(N'../../@id', N'int') as ComponentID , x.value(N'@nom',N'nvarchar(100)') as Nom , x.value(N'@valeur', N'nvarchar(100)') as Valeur from @x.nodes(N'/xml/box/components/component/variables/variable') t(x)
但是,如果您必须使用 XPath 来准确检索感兴趣的值:
However, if you must use an XPath that retrieves exactly the value of interest:
select x.value(N'@valeur', N'nvarchar(100)') as Valeur from @x.nodes(N'/xml/box[@stepId=sql:variable("@stepID")]/ components/component[@id = sql:variable("@componentID")]/ variables/variable[@nom="Enabled"]') t(x)
如果 stepID 和组件 ID 是列,而不是变量,则您应该在 XPath 过滤器中使用 sql:column() 而不是 sql:variable.请参阅在 XML 数据中绑定关系数据.
If the stepID and component ID are columns, not variables, the you should use sql:column() instead of sql:variable in the XPath filters. See Binding Relational Data Inside XML Data.
最后,如果您只需要检查是否存在,您可以使用 exist() XML 方法:
And finaly if all you need is to check for existance you can use the exist() XML method:
select @x.exist( N'/xml/box[@stepId=sql:variable("@stepID")]/ components/component[@id = sql:variable("@componentID")]/ variables/variable[@nom="Enabled" and @valeur="Yes"]')
这篇关于获取 SQL XML 值的 XPath的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!