<book>
<author>Bob Villa</author>
<author>Tom Whatever</author>
<author>Sarah kajdl</author>
</book>
How would I query for any record that had an author of Tom Whatever? Most query examples I've seen reference the value [x], but I want to search all authors.
DECLARE @author as varchar(100)
SET @author = 'Tom Whatever'
SELECT xmlfield.value('/book/author')
WHERE xmlfield.value('/book/author') = @author
Here is the answer
DECLARE @x XML
SET @x = '<book>
<author>Bob Villa</author>
<author>Tom Whatever</author>
<author>Sarah kajdl</author>
</book>'
DECLARE @author AS VARCHAR(100)
SET @author = 'Tom Whatever'
SELECT c.value('.' ,'VARCHAR(100)')
FROM @x.nodes('book/author') AS t(c)
WHERE c.value('.' ,'VARCHAR(8000)') = @author
If you just want the value from an XML variable you can use the value method directly and use sql:variable in your XPath expression:
SELECT @XML.value('(/book/author[.=sql:variable("@author")])[1]', 'nvarchar(50)')
If you have your XML in a table and want the rows where there is an author you can use exist.
select *
from YourTable
where XMLCol.exist('/book/author[.=sql:variable("@author")]') = 1
And then you can of course combine them.
select XMLCol.value('(/book/author[.=sql:variable("@author")])[1]', 'nvarchar(50)')
from YourTable
where XMLCol.exist('/book/author[.=sql:variable("@author")]') = 1
