sql server 中xml 資料類型的insert、update、delete

標籤：

近日對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

sql server 中xml 資料類型的insert、update、delete