Fetch a value from a column which contains XML

Question

How to fetch a value from a column which contains XML in SQL Server? below is my sample XML column value and the id's can be swap anytime(101,100) or (201,100,101,321).

<Questions>
 <item id="101">Yes</item>
 <item id="100">No</item>
</Questions>

I want to fetch a value based on Id. Like fetching Yes from id=101. Sample code much appreciated.

I tried with below sample, but Unable to retrieve value "Yes"

select Y.value('@item[1]','varchar[3]') as valT from tbl_storeXML s cross apply s.Questions.nodes('Questions/item') as X(Y) where e.empId=256 and Y.value('@id','int')=101

Please help on this. Ps. It's not a home work, I am learning handling xml in sql server.

Does this answer your question? [Select data from XML file as table in TSQL](https://stackoverflow.com/questions/7649301/select-data-from-xml-file-as-table-in-tsql) — Luuk, Apr 20 '22 at 07:02
Does this answer your question? [How can I query a value in SQL Server XML column](https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column) — Gedeon Mutshipayi, Apr 20 '22 at 07:03
As per the question guide, do some research to find out if your question was asked earlier (which might have your answer) — Luuk, Apr 20 '22 at 07:05

score 0 · Accepted Answer · answered Apr 20 '22 at 08:14

Use of the value is not done correct, you do:

Y.value('@id','int')

This should be: Y.value('(@id)[1]','int')

round braces around @id, see: docs: value() Method

and Y.value('item[1]','varchar[3]').

This should be: Y.value('(@item)[1]','varchar(3)').

The @ is removed because item is not an attribute
varchar should have round braces, not square braces.

Your try, after changes will become:

select 
   Y.value('(item)[1]','varchar(3)') as valT 
from tbl_storeXML s 
cross apply s.Questions.nodes('Questions/item') as X(Y) 
where e.empId=256 and Y.value('(@id)','int')=101

This is not tested, because I do not have those tables. (I do think Y.value('(item)[1]','varchar(3)') might need to be written as Y.value('(.)[1]','varchar(3)') )

But the same approach can be seen in this DBFIDDLE

DECLARE @xml XML = '<Questions>
 <item id="101">Yes</item>
 <item id="100">No</item>
</Questions>';

select 
  X.y.value('(@id)[1]','VARCHAR(20)') id,
  X.y.value('(.)[1]','VARCHAR(20)') value
from  @xml.nodes('Questions/item') as X(y);

output:

id	value
101	Yes
100	No

Fetch a value from a column which contains XML

1 Answers1