UPDATE XmlsampleDB.dbo.Arthur
SET BooksXML.modify('
insert
<book name="湘妃剑" />
into
/books[1]')
WHERE
region = '台北'
DECLARE @bn NVARCHAR(max) = '剑'
SELECT
ArthurName,
Region,
CONVERT(NVARCHAR(max),BooksXML.query('
for $s in /books/book
let $n := data($s/@name)
let $p := concat($n,"-")
where $s/@name[contains(.,sql:variable("@bn"))]
order by $p
return $p')) AS BookList
FROM
XmlsampleDB.dbo.Arthur
ArthurName Region BookList
金庸 香港 书剑恩仇录- 碧血剑-
古龙 台北 三少爷的剑- 多情剑客无情剑- 流星·蝴蝶·剑- 浣花洗剑录- 湘妃剑-
梁羽生 香港 七剑下天山-
UPDATE XmlsampleDB.dbo.Arthur
SET BooksXML.modify('
delete
books/book[11]')
WHERE
region = '台北'
DECLARE @bn NVARCHAR(max) = '剑'
SELECT
ArthurName,
Region,
CONVERT(NVARCHAR(max),BooksXML.query('
for $s in /books/book
let $n := data($s/@name)
let $p := concat($n,"-")
where $s/@name[contains(.,sql:variable("@bn"))]
order by $p
return $p')) AS BookList
FROM
XmlsampleDB.dbo.Arthur
ArthurName Region BookList
金庸 香港 书剑恩仇录- 碧血剑-
古龙 台北 三少爷的剑- 多情剑客无情剑- 流星·蝴蝶·剑- 浣花洗剑录-
梁羽生 香港 七剑下天山-
UPDATE XmlsampleDB.dbo.Arthur SET BooksXML.modify(' replace value of /books[1]/book[11]/@name[1] with "长生剑"') WHERE region = '台北' DECLARE @bn NVARCHAR(max) = '剑' SELECT ArthurName, Region, CONVERT(NVARCHAR(max),BooksXML.query(' for $s in /books/book let $n := data($s/@name) let $p := concat($n,"-") where $s/@name[contains(.,sql:variable("@bn"))] order by $p return $p')) AS BookList FROM XmlsampleDB.dbo.Arthur
ArthurName Region BookList 金庸 香港 书剑恩仇录- 碧血剑- 古龙 台北 三少爷的剑- 多情剑客无情剑- 流星·蝴蝶·剑- 浣花洗剑录- 长生剑- 梁羽生 香港 七剑下天山-