問題描述

我有以下 XML 數據，我對其進行零控制.請注意，它基本上是屬性組的集合.我需要選擇一個屬性的值，其中另一個屬性的值為true".問題是，沒有什么可分組的，我不知道如何正確關聯.

I have the following XML data, which I have zero control over. Note that it's basically a collection of property groups. I need to select the value of one property where the value of another property is 'true'. The problem is, there's nothing to group on, and I cannot figure out how to associate things correctly.

這是 XML 數據，以及我目前想到的查詢:

Here's the XML Data, and the query I came up with so far:

DECLARE @xml xml = '
<Container>
  <Collection>
    <ItemName>SomeItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>false</IsPersisted>
  </Collection>
  <Collection>
    <ItemName>AnotherItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>ItemFoo</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>BarBazItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>false</ExistsInDB>
  </Collection>
</Container>
'

;WITH XmlStuff AS (
    SELECT CAST(xmlShredded.colXmlItem.query('local-name(.)') AS nvarchar(4000)) as XmlNodeName,
        xmlShredded.colXmlItem.value('.', 'nvarchar(4000)') AS XmlNodeValue
    FROM @xml.nodes('/*/Collection/child::node()') as xmlShredded(colXmlItem) 
)
SELECT *
FROM XmlStuff 

現在，我需要做的是為ExistsInDB"為true"的每個分組獲取ItemName"值.請注意，第一個屬性集合中不存在ExistsInDB"屬性(即，應將其視為 NULL/false).

Now, what I need to do, is get the "ItemName" value for each grouping where "ExistsInDB" is 'true'. Note that the "ExistsInDB" property doesn't exist in the first property collection (i.e. it should be considered NULL/false).

所以在這種情況下，我需要查詢這個xml數據，并取回以下集合:

So in this case, I need to query this xml data, and get back the following set:

另一個項目ItemFoo

AnotherItem ItemFoo

我不應該得到SomeItem"或BarBazItem".

I should NOT get "SomeItem" or "BarBazItem".

我一直在努力弄清楚如何制定獲取所有 ItemName 值，其中關聯的 ExistsInDB 值都存在且為真"的查詢.

I've been beating my head against the desk trying to figure out how to formulate the query for "Get all ItemName values where the associated ExistsInDB value is both present, and true".

這甚至可能嗎?

推薦答案

也許試試兄弟匹配

/Container/Collection/ItemName[../ExistsInDB='true']

獲取 ItemName 元素，其父元素包含等于true"的 ExistsInDb 子元素.

That gets ItemName elements whose parents contain an ExistsInDb child equal to "true".

DECLARE @xml xml = '
<Container>
  <Collection>
    <ItemName>SomeItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>false</IsPersisted>
  </Collection>
  <Collection>
    <ItemName>AnotherItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>ItemFoo</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>BarBazItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>false</ExistsInDB>
  </Collection>
</Container>
';

SELECT node.value('.', 'nvarchar(100)')
FROM @xml.nodes('/Container/Collection/ItemName[../ExistsInDB="true"]') AS x(node) 

這篇關于根據同級值查詢 XML 數據以獲取信息的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！