根据同级值查询 XML 数据以获取信息

2021-09-10 00:00:00 tsql sql-server sqlxml

我有以下 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)

相关文章