How to get xml value from table column

Question

<SSRSReport> <OutputType>email</OutputType> <email> <emailTo>xyz.com</emailTo> <emailReplyTo> [e-mail] </emailReplyTo> <emailSubject>status report</emailSubject> <emailBody> </email> </SSRSReport> 

You need help selecting a node value in sql from a table existing in a single xml type column. I already tried:

 select T.id, T.xml_data.value('(SSRSReport/email/@emailTo)[1]', 'varchar(50)') as PropertyName from abc as T where T.xml_data.exist('/email/emailTo') = 'xyz.com' 

but it returns the column with the property name as null.

Answer 1

You are pretty close, but since <emailTo> is an element (not an attribute), you need to use:

 select T.id, T.xml_data.value('(SSRSReport/email/emailTo)[1]', 'varchar(50)') as PropertyName 

Use /emailTo (not /@emailTo ) as the last part of an XPath expression.

In addition, the .exist() function can check for the presence of an XML element (or attribute), so you can check if the <emailTo> element <emailTo> (or does not exist), but you cannot compare with the value. So your WHERE invalid - you probably wanted:

 where T.xml_data.value('(SSRSReport/email/emailTo)[1]', 'varchar(50)') = 'xyz.com'