Как получить содержимое узла на основе содержимого одного из столбцов XML в базе данных?

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>

Я хочу получить все названия и только названия книг автора. Информация о книге хранится в столбце БД в виде данных XML. Каждый в отдельном ряду. Я пробовал разные звонки, но это не сработало. Самое близкое, что я получил, было:

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

Но это возвращает 4 строки с

"t1"
"t2"
""
""

Пожалуйста помоги

Answer 1

Попробуй это

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

Похоже, я неправильно понял функцию value(). Я продолжал помещать [1] вне одиночных кавычек, и это продолжало терпеть неудачу. Следующее производит то, что я искал:

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'

Спасибо RNA Team за предоставленный пример @t, я не знал, что вы можете объявить таблицу как переменную. Сэкономил мне много времени, потому что это позволило мне экспериментировать без реальных данных