問題描述
場景:我在 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)')
這篇關于如何在 SQL 語句中將變量傳遞到 XQuery 的 XPath的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！