CODEWITHSUNDEEP
sql-serverxml
jimmy8ball
jimmy8ball

Reputation: 756

SQL Server XML Data - Not Returning Value

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?

Upvotes: 0

Views: 248

Answers (1)

Thom A
Thom A

Reputation: 95906

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);

Upvotes: 1

Related Questions