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.

CodePudding user response：

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

1. 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: