Request multiple XML values

Question

<book> <author>Bob Villa</author> <author>Tom Whatever</author> <author>Sarah kajdl</author> </book> 

How would I request any entry that Tom Whatever was in? Most of the sample queries I've seen refer to 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 

Answer 1

Here's 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 

Answer 2

If you just need the value from the XML variable, you can directly use the value method and use sql:variable in the XPath expression:

 SELECT @XML.value('(/book/author[.=sql:variable("@author")])[1]', 'nvarchar(50)') 

If you have XML in the table and you need rows that have 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