CODEWITHSUNDEEP
sql-serverxmlt-sqlxpath
user135498
user135498

Reputation: 6133

Need help on a T-SQL XPath query

I'm hoping somebody can help me solve a syntax problem with a TSQL xpath query.

Given the following xml:

<emails>
    <email></email>
    <email></email>
</emails>

I'm trying to execute the following query:

select * from messages where SendTo.value('(/emails/email)[1]', 'nvarchar(max)') like '%[email value]%'

My query looks only in the first email element and it needs to look in all email elements.

Thanks, Chris

Upvotes: 0

Views: 1229

Answers (1)

praveen
praveen

Reputation: 12281

Try this :-

select * from messages
CROSS APPLY SendTo.nodes('/emails/email') AS Artists(a)
where a.value('(text())[1]', 'nvarchar(max)') like '%[email value]%'

Demo in SQL FIDDLE

Upvotes: 1

Related Questions