<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>
不推荐使用相同的元素
<Root>
<add key="ProductDescription" value="1" />
<add key="ProductDescription" value="2" />
</Root>
modify (XML_DML)
此方法使用 XML DML 语句在 xml 数据中插入、更新或删除节点。xml 数据类型的 modify() 方法只能在 UPDATE 语句的 SET 子句中使用。
insert
Expression1 (
{as first | as last} into | after | before
Expression2
)
Expression1 (
{as first | as last} into | after | before
Expression2
)
CREATE TABLE TEST
(
A INT,
B XML
)
(
A INT,
B XML
)
insert test (a,b)
select 1,'<root/>'
--插入元素
update test
set b.modify('
insert (<name>b2</name>)
as first
into (/root)[1]'
)
where a = 1
--插入属性
update test
set b.modify('
insert (attribute id{"001"})
into (/root)[1]'
)
where a = 1
--插入注释
update test
set b.modify('
insert <!-- comment -->
into (/root/name[1])[1]'
)
where a = 1
--使用IF
update test
set b.modify('
insert
if (count(/root/name)<=2)
then element address {"China"}
else ()
as last
into (/root)[1]'
)
where a = 1
select 1,'<root/>'
--插入元素
update test
set b.modify('
insert (<name>b2</name>)
as first
into (/root)[1]'
)
where a = 1
--插入属性
update test
set b.modify('
insert (attribute id{"001"})
into (/root)[1]'
)
where a = 1
--插入注释
update test
set b.modify('
insert <!-- comment -->
into (/root/name[1])[1]'
)
where a = 1
--使用IF
update test
set b.modify('
insert
if (count(/root/name)<=2)
then element address {"China"}
else ()
as last
into (/root)[1]'
)
where a = 1
--替换
update test
set b.modify('
replace value of (/root/address/text())[1]
with (
if (count(/root/name)<=2)
then
"beijin"
else
"taiwan"
)'
)
where a = 1
update test
set b.modify('
replace value of (/root/address/text())[1]
with (
if (count(/root/name)<=2)
then
"beijin"
else
"taiwan"
)'
)
where a = 1
--删除
update test
set b.modify('
delete /root/address
')
where a = 1
update test
set b.modify('
delete /root/address
')
where a = 1