SQL Server XML Data - Not Returning Value

Question

I have a simple script that is returning data from an XML column.

 SELECT 
 CONVERT(XML,[DATA]).value('(/FormSection/FormSection/Form/Breathing/SmokingStatus/node())[1]', 'nvarchar(max)') [SMOKING STATUS]
 FROM MYTABLE

Which works completely fine when a value has been recorded textually like below and the value non-smoker is returned to the grid:

 <SmokingStatus points="null" code="nonSmoker">non smoker</SmokingStatus>

However when I find values like false or true like in the xml below, its returning NULL

 <RequestCessationYes value="false" />

Could anyone point out what I am missing please?

Thom A · Accepted Answer · 2019-09-04 08:30:07Z

1

You need to use the @ symbol prior to the element's name to get values like that:

SELECT V.X.value('(RequestCessationYes/@value)[1]','varchar(5)') AS [value]
FROM (VALUES(CAST('<RequestCessationYes value="false" />' AS xml)))V(X);

answered Sep 4, 2019 at 8:30

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server XML Data - Not Returning Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related