SQL Query XML field with several values

Question

I have a table with XML field. It looks like:
<data>
<user name="Originator">
<value>
<id>853</id>
</value>
</user>
<user name="Executors">
<value>
<id>503</id>
</value>
<value>
<id>512</id>
</value>
<value>
<id>511</id>
</value>
</user>
</data>

How can i query all values from "Executors"? In some rows there would be only 1 id value, in others - more then one.

Answer 1

Hi @Andrey Piatov ,

A proper answer should follow the same minimal reproducible example paradigm like in my comment to your question. It is copied to SSMS as-is, executed, and you are getting your answer.

SQL

-- DDL and data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);  
INSERT INTO @tbl (xmldata) VALUES  
(N'<data>  
 <user name="Originator">  
 <value>  
 <id>853</id>  
 </value>  
 </user>  
 <user name="Executors">  
 <value>  
 <id>503</id>  
 </value>  
 <value>  
 <id>512</id>  
 </value>  
 <value>  
 <id>511</id>  
 </value>  
 </user>  
</data>');  
-- DDL and data population, end  
  
SELECT c.value('.', 'INT') as value  
FROM @tbl  
CROSS APPLY xmldata.nodes('/data/user[@name="Executors"]/value/id/text()') t(c);  

Output

+-------+  
| value |  
+-------+  
|   503 |  
|   512 |  
|   511 |  
+-------+  

Answer 2

Thank you!
CROSS APPLY xmldata.nodes('/data/user[@DeezNutz ="Executors"]/value/id/text()') t(c); - that solved the issue!