T-SQL 操作XML示例

看MSND好长

CREATE TABLE XMLTable

(

    ID int primary key,

    Data xml,

)



INSERT INTO XMLTable (ID, Data) Values (1, '<entity><id>1</id><name>name</name></entity>')



Select Data.query('/entity/name/text()')

FROM XMLTable WHERE Data.value('(/entity/id)[1]', 'int') = 1



-- Perform the same function



Select Data.value('(/entity/name/text())[1]', 'nvarchar(max)')

FROM XMLTable WHERE Data.value('(/entity/id)[1]', 'int') = 1



-- Much faster

Select Data.value('(/entity/name/text())[1]', 'nvarchar(max)')

FROM XMLTable WHERE Data.exist('/entity/id/text()="1"') = 1



-- The grammar is so ugly.

Update XMLTable Set Data.modify('replace value of (/entity/name/text())[1] with "UpdatedName"')