sql - 使用 XPATH/XQUERY 选择具有属性 X 的元素的所有值

Question

我对 XPath 和 XQuery 比较陌生。我正在处理存储在名为 tblXML 的表中的 XML。XML 按 caseID 存储在此表中。对于这个例子，这个值是唯一的。XML 本身存储在标记为 Data 的列中。

如果您select * from tblXML where caseID = '12345'，您会看到:

caseID        data
------        ------
12345         <root><patient><diagnosis preferred_name="diagnosis" tier="1">Melanoma</diagnosis> (XML file stored as blob in database)

如果您深入研究 XML，它会看起来像这样。例如，这是 caseID '12345' 的 data 列中的 XML:

<root>
    <patient>
        <diagnosis preferred_name="diagnosis" tier="1">Melanoma</diagnosis>
        <month_of_birth preferred_name="month_of_birth" tier="1">02</month_of_birth>
        <day_of_birth preferred_name="day_of_birth" tier="2">01</day_of_birth>
        <year_of_birth preferred_name="year_of_birth" tier="1">1960</year_of_birth>
        <new_tumor_events>
            <month_of_nte preferred_name="month_of_nte" tier="1">12</month_of_nte>
            <day_of_nte preferred_name="day_of_nte" tier="2">30</day_of_nte>
            <year_of_nte preferred_name="year_of_nte" tier="1">1994</year_of_nte>
        </new_tumor_events>
        <follow_ups>
            <follow_up>
                <month_of_fu preferred_name="month_of_fu" tier="1">12</month_of_fu>
                <day_of_fu preferred_name="day_of_fu" tier="2">31</day_of_fu>
                <year_of_fu preferred_name="year_of_fu" tier="1">1995</year_of_fu>
            </follow_up>
            <follow_up>
                <month_of_fu preferred_name="month_of_fu" tier="1">12</month_of_fu>
                <day_of_fu preferred_name="day_of_fu" tier="2">31</day_of_fu>
                <year_of_fu preferred_name="year_of_fu" tier="1">1996</year_of_fu>
            </follow_up>
        </follow_ups>
    </patient>
</root>

目标:我正在尝试为 XML 文档的所有级别/节点的所有元素选择所有值，这些元素的 @Tier 属性为 “1”。我希望看到每个值都输出到它自己的行中名为“值”的列中，如下所示:

caseID     Value
------     ------
12345      Melanoma
12345      02
12345      1960
12345      12
12345      1994
12345      12
12345      1995
12345      12
12345      1996

这是类似于我正在尝试编写的 SQL:

DECLARE @tier int 
SET @tier = '1' --To search by tier explicitly via variable

    select 
        x.caseID,
    bar.value('(//*[@tier = sql:variable("@tier")])[1]','varchar(max)') as value
from tblXML as x
    CROSS APPLY data.nodes('//*:patient') AS foo(bar)
where
    x.caseID = '12345'
    and x.data.value('(//@tier)[1]', 'varchar(3)') = '1'

这给了我...

case ID        value
------         ------  
12345          Melanoma

...但不是我正在寻找的其他行。

我认为我的困惑集中在单例 [1] 和我的 CROSS APPLY 语句中的上下文节点强加的约束上。我不确定如何获取第 1 层文档中的所有内容，而不仅仅是查询找到的第 1 层的第一个实例。

对于这个例子，我知道我的 CROSS APPLY 语句中的上下文节点集中在 XML 的 patient 部分，但我希望能够在 XML 树的所有级别进行搜索上和下。我知道 @Tier 的 // 让我可以搜索文档的所有级别，但我的上下文节点似乎在某种程度上驱动/限制了我对文档的深入程度我可以搜索的文档。

据我了解，我可以深入研究 XML(follow_up 也许？)，然后自上而下搜索。但是，最深的节点将从 caseID 更改为 caseID，将 XML 文档更改为 XML 文档。有没有办法将我的 CROSS APPLY 语句通配到给定 XML 文档中的最深节点，然后在该文档中查找任何出现的 @Tier，然后检索其对应的值？

我意识到该方法缺乏优雅且性能不佳。到目前为止，我还没有找到明确的文档来提供我正在寻找的结果。

预先感谢您的帮助。

编辑:我应该指定此 SQL 将成为 SQL Server 中表值函数的核心。

Answer 1

定义一个键:

<xsl:key name="t" match="*[@tier]" use="@tier"/>

并使用

找到所有 teir=1 元素

<xsl:for-each select="key('t', '1')">
  ....
</xsl:for-each>