从SQL Server中的xml列中提取XML标记值

-- 创建一个测试表
CREATE TABLE xmlTable
(
    ID INT PRIMARY KEY,
    xmlData XML
)

-- 插入一些XML数据
INSERT INTO xmlTable(ID, xmlData)
VALUES (1, '<bookstore>
                <book>
                    <title>Harry Potter and the Philosopher&apos;s Stone</title>
                    <author>J.K. Rowling</author>
                </book>
                <book>
                    <title>The Great Gatsby</title>
                    <author>F. Scott Fitzgerald</author>
                </book>
            </bookstore>')

-- 使用XQuery提取XML标记值
SELECT
    xmlData.value('(/bookstore/book[1]/title)[1]', 'VARCHAR(100)') AS Title,
    xmlData.value('(/bookstore/book[1]/author)[1]', 'VARCHAR(100)') AS Author
FROM
    xmlTable
WHERE
    ID = 1

Title                                           Author
-----------------------------------------------------------
Harry Potter and the Philosopher's Stone        J.K. Rowling