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