問(wèn)題描述

Stackoverflow 上的某個(gè)地方有一個(gè)問(wèn)題，雖然我現(xiàn)在找不到了，但它提醒發(fā)布者 .value 不會(huì)返回 .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.

那是因?yàn)?.value 總是寫(xiě)成要求 [1] 項(xiàng)，其中 .exist 到處可見(jiàn).

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

假設(shè)一個(gè)包含兩個(gè)客戶的 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.

事情變得更加復(fù)雜，其中有多個(gè) 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>

當(dāng)您可以在其他級(jí)別找到 /IDInfo 節(jié)點(diǎn)時(shí)，情況會(huì)更加復(fù)雜:

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>

最終結(jié)果是一樣的.我需要一個(gè)查詢來(lái)返回 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

獎(jiǎng)金聊天

當(dāng)我兩年前設(shè)計(jì)系統(tǒng)并選擇使用 XML 數(shù)據(jù)類型時(shí)，我認(rèn)為在緊急情況下它會(huì)很有用.我可以使用一些 XPath 來(lái)過(guò)濾原始 xml.我忘記了 XPath 和 SQL Server 中的 XPath 是多么不可能.四個(gè)小時(shí)盯著文檔和網(wǎng)站；我又餓又累.

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

這篇關(guān)于如何返回 SQL Server XPath 中存在的值?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！