How to get node content based on sibling content from xml column in database?

Question

<book>
 <author>a1</author>
 <title>t1</title>
</book>
<book>
 <author>a1</author>
 <title>t2</title>
</book>
<book>
 <author>a2</author>
 <title>t3</title>
</book>
<book>
 <author>a3</author>
 <title>t4</title>
</book>

I want to get all titles and only titles of books by an author. The book info is stored in a db column as xml data. Each is in a different row. I tried various value calls and it didn't work at all. The closest I got was:

select parameters.query('//book[author="a1"]/title/text()') from tablename

But this returns 4 rows with

"t1"
"t2"
""
""

Please help

Answer 1

Try this

DECLARE @t TABLE (
    Data XML
)
INSERT INTO @t (Data)
SELECT '<book>
 <author>a1</author>
 <title>t1</title>
</book>
<book>
 <author>a1</author>
 <title>t2</title>
</book>
<book>
 <author>a2</author>
 <title>t3</title>
</book>
<book>
 <author>a3</author>
 <title>t4</title>
</book>'

SELECT
    x.value('author[1]', 'VARCHAR(20)') AS Author
    ,x.value('title[1]', 'VARCHAR(20)') AS Title
FROM @t t
CROSS APPLY Data.nodes('/book') a(x)
WHERE x.value('author[1]', 'VARCHAR(20)') = 'a1'

/*
Author  Title
------  -------
a1         t1
a1         t2
*/

Answer 2

Looks like I misunderstood the value() function. I kept on putting the [1] outside the single quotes and it kept failing. The following produces what I was looking for:

DECLARE @t TABLE (
Book XML
)
INSERT INTO @t (Book) values ( '<book><author>a1</author><title>t1</title></book>')
INSERT INTO @t (Book) values ( '<book><author>a1</author><title>t2</title></book>')
INSERT INTO @t (Book) values ( '<book><author>a2</author><title>t3</title></book>')
INSERT INTO @t (Book) values ( '<book><author>a3</author><title>t4</title></book>')
select Book.value('(/book/title)[1]', 'VARCHAR(20)') as title from @t t
where Book.value('(/book/author)[1]', 'VARCHAR(20)') = 'a1'

Thanks to RNA Team for giving the @t example, I wasn't aware that you could declare a table as a variable. Saved me lot of time because it allowed me to experiment without the real data