如何在 SQL 语句中将变量传递到 XQuery 的 XPath

2021-10-02 00:00:00 xpath xquery sql-server sqlxml

场景:我在 MSSQL 数据库中有一个 xml 列，我必须使用 XQuery 解析该单元格的 XML 数据.

Scenario: I have an xml column in MSSQL database which I have to parse the XML data of that cell using XQuery .

     Xml content : <AnchoredXml xmlns="urn:schema:Microsoft.Rtc.Management.ScopeFramework.2008" SchemaWriteVersion="2">
  <Key ScopeClass="Global">
    <SchemaId Namespace="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008" ElementName="Topology" />
    <AuthorityId Class="Host" InstanceId="00000000-0000-0000-0000-000000000000" />
  </Key>
  <Dictionary Count="1">
    <Item>
      <Key />
      <Value Signature="b1ac04f7-d8f0-4300-86cf-fb2b3383536c">
        <Topology xmlns="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008">
          <InternalDomains AllowAllDomains="false" DefaultDomain="ocsqa.com">
            <InternalDomain Name="ocsqa.com" Authoritative="false" AllowSubDomains="false" />
          </InternalDomains>
          <Sites>
            <CentralSite SiteId="1">
              <Name>LyncSite</Name>
              <Location />
            </CentralSite>
          </Sites>
          <Clusters>

这是该单元格中xml内容的一段数据.

This is a piece of data of the xml content in that one cell.

我使用下面的查询来遍历上面xml的节点:

I am using below query to traverse the nodes of above xml:

select @cluster = @Items.query('/DocItemSet/DocItem/Data/*[@SchemaWriteVersion="2"]/*[2]/*[1]/*[2]/*[1]/*[3]')

上述查询的输出是:

    <p1:Cluster xmlns:p1="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008" RequiresReplication="true" RequiresSetup="true" Fqdn="XXXX.ocsqa.com">
  <p1:ClusterId SiteId="1" Number="1" />
  <p1:Machine OrdinalInCluster="1" Fqdn=" XXXX.ocsqa.com">
    <p1:NetInterface InterfaceSide="Primary" InterfaceNumber="1" IPAddress="0.0.0.0" />
    <p1:NetInterface InterfaceSide="External" InterfaceNumber="1" IPAddress="0.0.0.0" />
    <p1:NetInterface InterfaceSide="Pstn" InterfaceNumber="1" IPAddress="0.0.0.0" />
  </p1:Machine>
</p1:Cluster>
<p2:Cluster xmlns:p2="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008" RequiresReplication="true" RequiresSetup="true" Fqdn=" XXXX2.ocsqa.com">
  <p2:ClusterId SiteId="1" Number="2" />
  <p2:Machine OrdinalInCluster="1" Fqdn=" XXXX2.ocsqa.com">
    <p2:NetInterface InterfaceSide="Primary" InterfaceNumber="1" IPAddress="0.0.0.0" />
    <p2:NetInterface InterfaceSide="External" InterfaceNumber="1" IPAddress="0.0.0.0" />
    <p2:NetInterface InterfaceSide="Pstn" InterfaceNumber="1" IPAddress="0.0.0.0" />
  </p2:Machine>
</p2:Cluster>
<p3:Cluster xmlns:p3="urn:schema:Microsoft.Rtc.Management.Deploy.Topology.2008" RequiresReplication="true" RequiresSetup="true" Fqdn=" XXXX2.ocsqa.com">
  <p3:ClusterId SiteId="1" Number="3" />
  <p3:Machine OrdinalInCluster="1" Fqdn=" XXXX2.ocsqa.com" />
</p3:Cluster>

现在使用下面提到的查询:

Now using query mentioned as below : 

select @fqdn = @cluster.value('(./*/*/@Fqdn)[1]','nvarchar(20)') Select @fqdn

注意上面查询中突出显示的索引号.使用这个查询，我们将能够获得第一个可用的 xml 集群，同样我也想寻找其他集群.

Note the highlighted index no in above query. Using this query we will be able to achieve the first Cluster available in xml, similarly I wanted to look for other Clusters as well.

所以我想在 while 循环中使用这个查询.为此，我必须传递一个变量而不是硬编码的 int 值.类似于以下内容:

So I wanted to use this query in while loop. For which I have to pass a variable instead of hardcoded int value. Something similar as below :

select @fqdn = @cluster.value('(./*/*/@Fqdn)[sql:variable("@test")]','nvarchar(20)')

我参考了一些帖子如何将 XPath 与 Oracle XMLTable 中的变量一起使用?http://www.jasonstrate.com/2011/01/xquery-for-the-non-expert-variable-use/

但我收到如下错误:

消息 2389，级别 16，状态 1，第 35 行XQuery [value()]: 'value()' 需要一个单例(或空序列)，找到类型为 'xdt:untypedAtomic *' 的操作数如何将变量传递到 SQL 语句的 XQuery 中?

Msg 2389, Level 16, State 1, Line 35 XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *' How to pass a variable into a XQuery of SQL statement?

推荐答案

你需要告诉 SQL Server 你只对单个节点感兴趣.在末尾添加 [1].

You need to tell SQL Server that you are only interested in the a single node. Add a [1] at the end.

@cluster.value('(./*/*/@Fqdn)[sql:variable("@test")][1]','nvarchar(20)')

相关文章