How do I select the value of an ID element in XPath?

Question

Given an XML with the following set up:

<eCrow.CrowGroup CorrelationID="ec367934-e7bd-4213-b0e5-d149c57eec61" > 
    <eCrow.01>fu</eCrow.01> 
    <eCrow.02>bar</eCrow.02> 
    <eCrow.03 CorrelationID="bfe7d35b-bbc1-4591-8d0d-9d42252039bc" >03003</eCrew.03> 
</eCrow.CrowGroup>

How do I manage to get XPath to select the CorrelationID from within the node header: <eCrow.CrowGroup CorrelationID="ec367934-e7bd-4213-b0e5-d149c57eec61" >, NOT the CorrelationID from eCrow.03.

In regards to the link suggestion, I am probably doing something wrong but //eCrew.CrewGroup/*@CorrelationID just selects the entire node.

You have a typo in your XML, the closing tag for `eCrow.03` is named `eCrew.03`. — Luuk, Jul 14 '21 at 18:56
The XPATH expression to CorrelationID Is `/eCrow.CrowGroup/@CorrelationID`, see: [Getting attribute using XPath](https://stackoverflow.com/questions/4531995/getting-attribute-using-xpath) — Luuk, Jul 14 '21 at 18:57
Does this answer your question? [Getting attribute using XPath](https://stackoverflow.com/questions/4531995/getting-attribute-using-xpath) — Luuk, Jul 14 '21 at 18:58

Yitzhak Khabinsky · Answer 1 · 2021-07-14T20:48:06.207

1

Please try the following.

As already mentioned in the comments, I had to fix your XML to make it well-formed.

XQuery .value() method gives you the answer.

SQL

DECLARE @xml XML = 
N'<eCrow.CrowGroup CorrelationID="ec367934-e7bd-4213-b0e5-d149c57eec61">
    <eCrow.01>fu</eCrow.01>
    <eCrow.02>bar</eCrow.02>
    <eCrow.03 CorrelationID="bfe7d35b-bbc1-4591-8d0d-9d42252039bc">03003</eCrow.03>
</eCrow.CrowGroup>';

SELECT @xml.value('(/eCrow.CrowGroup/eCrow.03/@CorrelationID)[1]', 'VARCHAR(30)') AS CorrelationID
    , @xml.value('(/eCrow.CrowGroup/eCrow.03/@CorrelationID)[1]', 'uniqueidentifier') AS CorrelationID2;

Output

+--------------------------------+--------------------------------------+
|         CorrelationID          |            CorrelationID2            |
+--------------------------------+--------------------------------------+
| bfe7d35b-bbc1-4591-8d0d-9d4225 | BFE7D35B-BBC1-4591-8D0D-9D42252039BC |
+--------------------------------+--------------------------------------+

edited Jul 14 '21 at 20:48

answered Jul 14 '21 at 19:17

Yitzhak Khabinsky

18,471
2
15
21

You should probably just change the data type to `uniqueidentifier` – Charlieface Jul 14 '21 at 20:42
@Charlieface, good point, but it will make the output upper cased. Not sure if the OP needs that. – Yitzhak Khabinsky Jul 14 '21 at 20:43
No it won't, it will make it a Guid, which is 16-byte binary data. The client app is free to format that how it likes – Charlieface Jul 14 '21 at 20:45
Let's wait for the OP reply. – Yitzhak Khabinsky Jul 14 '21 at 20:46
@Charlieface, In the meantime I updated the answer to show both options. – Yitzhak Khabinsky Jul 14 '21 at 20:48

How do I select the value of an ID element in XPath?

1 Answers1