如何返回 SQL Server XPath 中存在的值?

2021-10-02 00:00:00 xpath xquery sql-server sql-server-2008-r2

Stackoverflow 上的某个地方有一个问题，虽然我现在找不到了，但它提醒发布者 .value 不会返回 .exist s 的值.

There is a question somewhere on Stackoverflow, although i cannot find it now, that reminded the poster that .value does not return the value that .exists.

那是因为 .value 总是写成要求 [1] 项，其中 .exist 到处可见.

That is because .value is always written as asking for the [1] item, where .exist looks everywhere.

假设一个包含两个客户的 xml 文档:

Given a hypothetical xml document containing two customers:

<Customer>
    <Name>Ian Boyd</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
</Customer>
<Customer>
    <Name>Kirsten</Name>
    <IDInfo>
        <IDType>3</IDType>
        <IDOtherDescription>Firearms Certificate</IDOtherDescription>
    </IDInfo>
</Customer>

我想为具有 IDType 的任何客户返回 Name、IDType 和 IDOtherDescription3(其他):

i want to return the Name, IDType, and IDOtherDescription for any customers who have an IDType of 3 (Other):

DECLARE @xml XML;
SET @xml = 
'<Customer>
    <Name>Ian Boyd</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
</Customer>
<Customer>
    <Name>Kirsten</Name>
    <IDInfo>
        <IDType>3</IDType>
        <IDOtherDescription>Firearms Certificate</IDOtherDescription>
    </IDInfo>
</Customer>'
--Wrap it up in a table, cause it makes it look more like my real situation
;WITH BatchReports AS (
    SELECT @xml AS BatchFileXml
)
SELECT
    BatchFileXml.value('(//Name)[1]', 'varchar(50)') AS Name,
    BatchFileXml.value('(//IDType)[1]', 'varchar(50)') AS IDType,
    BatchFileXml.value('(//IDOtherDescription)[1]', 'varchar(50)') AS IDOtherDescription,
    *
FROM BatchReports
--WHERE BatchFileXml.value('(//IDType)[1]', 'varchar(50)') = '3'
WHERE BatchFileXml.exist('//IDType[text()="3"]')=1

由于 .exist is 满足，所以返回一行:

Since the .exist is satisfied, it returns a row:

Name     IDType IDOtherDescription
-------- ------ --------------------
Ian Boyd      1 Firearms Certificate

除非那不是我想要的.我想要 IDType = 3 的值.

Except that's not what i wanted. I wanted the values where IDType = 3.

事情变得更加复杂，其中有多个 IDType 条目:

Things get even more complicated, where there are multiple IDType entries:

<Customer>
    <Name>Ian Boyd</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
</Customer>
<Customer>
    <Name>Kirsten</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>2</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>4</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>3</IDType>
        <IDOtherDescription>Firearms Certificate</IDOtherDescription>
    </IDInfo>
</Customer>

当您可以在其他级别找到 /IDInfo 节点时，情况会更加复杂:

And even more complicated when you can find /IDInfo nodes in other levels:

<Customer>
    <Name>Ian Boyd</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
    <ThirdPartyInfo>
       <IDInfo>
        <IDType>3</IDType>
            <IDOtherDescription>Sherrif Badge</IDOtherDescription>
       </IDInfo>
    </ThirdPartyInfo>
</Customer>
<Customer>
    <Name>Kirsten</Name>
    <IDInfo>
        <IDType>1</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>2</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>4</IDType>
    </IDInfo>
    <IDInfo>
        <IDType>3</IDType>
        <IDOtherDescription>Firearms Certificate</IDOtherDescription>
    </IDInfo>
</Customer>

最终结果是一样的.我需要一个查询来返回 exist 的 values:

The end result is the same. I need a query to return the values that exist:

Name     IDType IDOtherDescription
-------- ------ --------------------
Ian Boyd      3 Sherrif Badge
Kirsten       3 Firearms Certificate

奖金聊天

当我两年前设计系统并选择使用 XML 数据类型时，我认为在紧急情况下它会很有用.我可以使用一些 XPath 来过滤原始 xml.我忘记了 XPath 和 SQL Server 中的 XPath 是多么不可能.四个小时盯着文档和网站；我又饿又累.

Bonus Chatter

When i designed the system two years ago, and chose to use XML data type, i figured it would be useful when there's an emergency. I can use some XPath to filter through the raw xml. I forgot how impossible XPath, and XPath in SQL Server is. Four hours of staring at documentation and web-sites; i'm hungry and tired.

推荐答案

以下是您可以尝试的查询:

Here is the query you can try:

;WITH BatchReports AS (
    SELECT @xml AS BatchFileXml
)
SELECT a.BatchXml.value('(Name)[1]', 'varchar(50)') AS Name,
    a.BatchXml.value('(IDInfo/IDType)[1]', 'varchar(50)') AS IDType,
    a.BatchXml.value('(IDInfo/IDOtherDescription)[1]', 'varchar(50)') AS IDOtherDescription
FROM BatchReports b
CROSS APPLY b.BatchFileXml.nodes('Customer') A(BatchXml)
WHERE a.BatchXml.exist('IDInfo/IDType[text()=3]')=1

这里是从所有 XML 中检索必要信息的查询.

and here is the query which retrieve necessary information form all of the XMLs.

;WITH BatchReports AS (
    SELECT @xml AS BatchFileXml
)
SELECT A.BatchXml.value('(Name)[1]', 'varchar(50)') AS Name,
    B.BatchXml.value('(IDType)[1]', 'varchar(50)') AS IDType,
    B.BatchXml.value('(IDOtherDescription)[1]', 'varchar(50)') AS IDOtherDescription
FROM BatchReports X
CROSS APPLY X.BatchFileXml.nodes('Customer') A(BatchXml)
CROSS APPLY A.BatchXml.nodes('//IDInfo') B(BatchXml)
WHERE A.BatchXml.exist('IDInfo/IDType[text()=3]')=1
    AND B.BatchXml.exist('IDType[text()=3]')=1

相关文章