<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