MSSQL 中使用modify()方法,此方法使用XML DML语句在XML 数据中插入，更新或删除节点

Modify()

修改XML 文档的内容，使用此方法可以修改XML类型变量或列的内容，此使用XML DML 语句在XML数据中 插入，更新或者删除节点.XML数据类型的modify()只能在update语句的set 字句中使用

XML DML将下列区分大小写的关键字添加到XQuery中:

insert

delete

replace value of

注意：存在某些无妨修改，删除或者插入的 属性值例如

1)对于类型化或非类型化的 xml 而言，这样的属性有 xmlns、xmlns:* 和 xml:base。
2)仅对于类型化的 xml 而言，这样的属性有 xsi:nil 和 xsi:type。

下面列出了其他限制：
1)对于类型化或非类型化的 xml，插入 xml:base 属性将失败。
2)对于类型化的 xml，删除和修改 xsi:nil 属性将失败。对于非类型化的 xml，则可以删除此属性或修改此属性的值。
3)对于类型化的 xml，修改 xs:type 属性值将失败。对于非类型化的 xml，则可以修改此属性值。

1.插入(XML DML)

DECLARE @myDoc XML
SET @myDoc = '<Root>       
   <ProductDescription ProductID="1" ProductName="Road Bike">           
    <Features> </Features>           
</ProductDescription>     
</Root>'
/*将元素节点插入到文档中*/
--在Features里插入一个节点
SET @myDoc.modify(
'insert <Populate>Put your things into basket of bike</Populate>
into (/Root/ProductDescription/Features)[1]');
SELECT @myDoc;
--当前插入的节点为Features中第一个节点
SET @myDoc.modify('
insert <ride>people could ride bike</ride> 
as first into (/Root/ProductDescription/Features)[1]');
SELECT @myDoc;
--当前插入的节点为Features中最后一个节点
SET @myDoc.modify('
insert <function> people use it as transport</function> 
as last into (/Root/ProductDescription/Features)[1]');
SELECT @myDoc;
--当前插入的节点放在<ride>标签的后面
SET @myDoc.modify('
insert <sport>ride bike is a sport</sport>
after(/Root/ProductDescription/Features/ride)[1]');
SELECT @myDoc;
--------------------------------------
/*将多个元素插入到文档中*/
DECLARE @myDoc2 XML
SET @myDoc = '<Root>       
   <ProductDescription ProductID="1" ProductName="Road Bike">           
    <Features> </Features>           
</ProductDescription>     
</Root>'
DECLARE @NewFeatures XML
SET @NewFeatures = N'<ride>people could ride bike</ride>
					<sport>ride bike is a sport</sport>'

SET	@myDoc.modify('
insert sql:variable("@NewFeatures") 
into (/Root/ProductDescription/Features)[1]')
SELECT 	@myDoc;
------------------------------------
--插入属性到文档中
DECLARE @myDoc xml;           
SET @myDoc = 
'<Root>           
    <Location LocationID="10" >           
        <step>Manufacturing step 1 at this work center</step>           
        <step>Manufacturing step 2 at this work center</step>           
    </Location>           
</Root>';
--在Location节点中插入一个number属性,其值为5
SET @myDoc.modify('
insert attribute number {"5"} 
into (/Root/Location[@LocationID=10])[1]')
SELECT @myDoc;
--在Location节点中插入一个变量
DECLARE @hour INT
SET @hour = 2;
SET @myDoc.modify('
insert attribute hour {sql:variable("@hour")} 
into (/Root/Location[@LocationID=10])[1]')
SELECT @myDoc;
--------------------------------------------
--向表中类型为XML字段，增加新的节点
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T
CREATE TABLE T (i int, x xml);
go
INSERT INTO T VALUES(1,'<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>');
go
UPDATE T
	SET x.modify('
	insert <ride>people could ride bike</ride>  
	after (/Root/ProductDescription/Features/Maintenance)[1]') 
SELECT x.query('/Root/ProductDescription/Features') FROM T
-----------------------------------
--根据if 条件进行插入
DECLARE @myDoc xml;
SET @myDoc = 
'<Root>
    <Location LocationID="10" LaborHours="1.2" >
        <step>Manufacturing step 1 at this work center</step>
    <step>Manufacturing step 2 at this work center</step>
    </Location>
</Root>';
--满足当前条件添加一个新的hour属性
SET @myDoc.modify('
	insert 
	if (/Root/Location[@LocationID=10])
	then attribute hour {"5"}
	else ()
	into (/Root/Location[@LocationID=10])[1]')
SELECT @myDoc;
--满足当前条件添加一个新的节点
SET @myDoc.modify('
		insert
		if (count(/Root/Location/step) <= 2)
		then element step {"this is new step"} 
		else ()
		as first into (/Root/Location)[1]')
SELECT @myDoc;

2.替换(XML DML)

DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10" 
            LaborHours="1.1"
            MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
SELECT @myDoc;
--替换节点Location中LaborHours属性的值为100
SET @myDoc.modify('
replace value of (/Root/Location/@LaborHours)[1]
with "100"')
SELECT @myDoc;
--使用表更新另一个表中类型为XML的字段的属性
DECLARE @Friend TABLE
(
	ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	Friend XML
)
INSERT INTO @Friend SELECT '<Friends>
<friend name="junwenli" sex="man" age="23"></friend>
<friend name="jinhanliu" sex="man" age="24"></friend>
<friend name="fangcheng" sex="man" age="23"></friend>
</Friends>'

DECLARE @Temp TABLE
(
	ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	FriendName NVARCHAR(32)
)
INSERT INTO @Temp SELECT 'GuoHu';

UPDATE F
SET Friend.modify('replace value of (Friends/friend/@name)[1] with sql:column("T.FriendName")')
FROM @Friend F,@Temp T
WHERE F.ID = T.ID;
	
SELECT Friend FROM @Friend;

3.删除(XML DML)

DECLARE @myDoc xml
SET @myDoc = '<?Instructions for=TheWC.exe ?> 
<Root>
 <!-- instructions for the 1st work center -->
<Location LocationID="10" 
            LaborHours="1.1"
            MachineHours=".2" >Some text 1
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
--删除属性MachineHours
SET @myDoc.modify('
  delete /Root/Location/@MachineHours
')
SELECT @myDoc
--删除第二个step节点
SET @myDoc.modify('
delete /Root/Location/step[2]
')
SELECT @myDoc