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

Try something like this:

select   
    v.value('id[1]', 'int') as value  
from MyTable t  
cross apply t.MyXmlField.nodes('/data/user') u(u)  
cross apply u.nodes('value') v(v)  
where u.value('@name', 'varchar(max)') = 'Executors'  
order by value  

Or:

select   
    v.value('id[1]', 'int') as value  
from MyTable t  
cross apply t.MyXmlField.nodes('/data/user[@name="Executors"]/value') v(v)  
order by value  

Answer 3

But v.value('id[1]', 'int') - that will return only the 1st value, right? to get the second value i have to wright v.value('id[2]',int) etc.
However i need all values - and i don't know how much of them.

Answer 4

Try this:

   DECLARE @x xml =  
   '<data>  
   <user name="Originator">  
   <value>  
   <id>853</id>  
   </value>  
   </user>  
   <user name="Executors">  
   <value>  
   <id>503</id>  
   <id>514</id>  
   </value>  
   <value>  
   <id>512</id>  
   </value>  
   <value>  
   <id>511</id>  
   </value>  
   </user>  
   </data>'  
   SELECT T.c.value('(./text())[1]', 'int')  
   FROM   @x.nodes('/data/user[@name="Executors"]/value/id') AS T(c)  

Answer 5

Hi @Andrey Piatov
If you want the id value display in one row, then please check this sample:

DECLARE @tbl TABLE (Xml_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>'),  
 (N'<data><user name="Originator"><value><id>853</id></value></user><user name="Executors"><value><id>555</id></value><value><id>544</id></value><value><id>533</id></value><value><id>522</id></value></user></data>'),  
 (N'<data><user name="Originator"><value><id>853</id></value></user><user name="Executors"><value><id>511</id></value><value><id>566</id></value></user></data>'),  
 (N'<data><user name="Originator"><value><id>853</id></value></user></data>');  
  
--Use STRING_AGG (Need SQL Server 2017 (14.x) and later)  
;WITH CTE AS  
(  
 SELECT Xml_ID,n.x.value('id[1]','INT') AS Executors_Value  
 FROM @tbl t  
 OUTER APPLY xmldata.nodes('/data/user[@name="Executors"]/value') n(x) --Use CROSS APPLY instead of OUTER APPLY if you do not want NULL Executors_Value  
)  
SELECT Xml_ID,STRING_AGG(Executors_Value, ',') WITHIN GROUP (ORDER BY Executors_Value) AS Executors_Value  
FROM CTE  
GROUP BY Xml_ID  
  
--Try this query if not support STRING_AGG  
;WITH CTE AS  
(  
 SELECT Xml_ID,n.x.value('id[1]','INT') AS Executors_Value  
 FROM @tbl t  
 OUTER APPLY xmldata.nodes('/data/user[@name="Executors"]/value') n(x)  
)  
SELECT Xml_ID,  
       STUFF((SELECT ',' + CAST(Executors_Value AS VARCHAR) FROM CTE WHERE Xml_ID=T.Xml_ID FOR XML PATH('')),1,1,'')AS Executors_Value  
FROM CTE T  
GROUP BY Xml_ID  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.