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" ')
(
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" ')