您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server XML 資料型別 DML 操作完整指南：insert、delete、replace

編輯：SQL Server 2005

範例 XML 變數

DECLARE @x XML = N'

  
    Windows Step By Step
    Bill Zack
    49.99
  
  
    Developing ADO .NET
    Andrew Brust
    39.93
  
  
    Windows Cluster Server
    Stephen Forte
    59.99
  
';

1. xml.modify('insert ...')

A. 位置插入元素

SET @x.modify('insert  as first into (/catalog/book)[1]');
SET @x.modify('insert  as last into (/catalog/book)[1]');
SET @x.modify('insert  before (/catalog/book/author)[1]');
SET @x.modify('insert  after (/catalog/book/author)[1]');

四個關鍵字：as first、as last、before、after。

B. 批次插入多元素

-- 變數法
DECLARE @new XML = N'onetwo';
SET @x.modify('insert sql:variable("@new") into (/catalog/book)[1]');
-- 直接法
SET @x.modify('insert (one,two) into (/catalog/book/author)[1]');

C. 插入屬性

DECLARE @v NVARCHAR(10) = N'變數插入';
SET @x.modify('insert attribute var {sql:variable("@v")} into (/catalog/book)[1]');
SET @x.modify('insert attribute name {"直接插入"} into (/catalog/book/title)[1]');
SET @x.modify('insert (attribute Id {"多值1"}, attribute name {"多值2"}) into (/catalog/book/author)[1]');

D. 插入文字節點 / 註解 / 處理指令

SET @x.modify('insert text{"文字內容"} as first into (/catalog/book)[1]');
SET @x.modify('insert  before (/catalog/book/title)[1]');
SET @x.modify('insert  before (/catalog/book/title)[1]');

E. 條件式插入

SET @x.modify('
insert
if ((/catalog/book/title)[2]) then text{"第2本存在"}
else text{"第2本不存在"}
into (/catalog/book/price)[1]');

2. xml.modify('delete ...')

SET @x.modify('delete /catalog/book/@category');           -- 刪除屬性
SET @x.modify('delete /catalog/book/title[1]');             -- 刪除元素
SET @x.modify('delete /catalog/book/author[1]/text()');     -- 刪除文字內容
SET @x.modify('delete /catalog/book[2]');                   -- 刪除整個節點

3. xml.modify('replace value of ...')

SET @x.modify('replace value of (/catalog/book/@category)[1] with "新分類"');
SET @x.modify('replace value of (/catalog/book/author/text())[1] with "新作者"');
SET @x.modify('
replace value of (/catalog/book/@category)[2] with
  if (count(/catalog/book) > 4) then "多於4本" else "不超過4本"');