How to return the value that exists in SQL Server XPath?

Question

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.

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

Example

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>

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

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

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

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

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>

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>

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

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.

Hamlet Hakobyan · Accepted Answer · 2014-05-05T23:55:55.237

1

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

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

edited May 05 '14 at 23:55

answered May 05 '14 at 23:33

Hamlet Hakobyan

32,965
6
52
68

1

Is there someplace i could go that could explain what is going on here? I was hoping by asking the question, and getting an answer, i could learn something. But i have no concept of what this is doing. i know that `CROSS APPLY` is the moral equivalent of an *inner join* (where you're allowed to join to something that isn't a table). But could not explain to anyone what is going on here; nor could i re-use it anywhere else. (Bonus: As evidenced by [a question i asked and was answered a year ago](http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes) for the same system) – Ian Boyd May 06 '14 at 13:21
Which part of the query needs to be explained? The link, that mentioned above, somehow explains the main approach. – Hamlet Hakobyan May 06 '14 at 13:36

How to return the value that exists in SQL Server XPath?

Example

Bonus Chatter

1 Answers1

Linked