SQL Server 2005查询XML列数据

-- 提取整个XML列
SELECT xmlColumn FROM TableName WHERE id = 1

-- 使用value()方法提取标量值
SELECT xmlColumn.value('(/root/element)[1]', 'varchar(50)') 
FROM TableName

-- 查询特定节点
SELECT xmlColumn.query('//book/title') 
FROM Books
WHERE xmlColumn.exist('//book[@category="IT"]') = 1

-- 使用nodes()方法分解XML
SELECT 
    b.book.value('title[1]', 'varchar(100)') as Title,
    b.book.value('author[1]', 'varchar(50)') as Author
FROM 
    Books
CROSS APPLY 
    xmlColumn.nodes('/catalog/book') as b(book)

-- 创建XML索引
CREATE PRIMARY XML INDEX idx_xmlcol ON TableName(xmlColumn)
CREATE XML INDEX idx_xmlcol_path ON TableName(xmlColumn)
USING XML INDEX idx_xmlcol FOR PATH

-- 使用WITH XMLNAMESPACES处理命名空间
;WITH XMLNAMESPACES('uri' as ns)
SELECT xmlColumn.value('(/ns:root/ns:element)[1]', 'int')
FROM TableName

-- 使用modify()方法
UPDATE TableName
SET xmlColumn.modify('
    replace value of (/root/element/text())[1]
    with "new value"
')
WHERE id = 1