近日对SQL操作XML作了如下整理：

1、插入 XML

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

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

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