SQL XML DELETE

--A. 从存储在非类型化的 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>'
SELECT @myDoc

-- delete an attribute
SET @myDoc.modify('
  delete /Root/Location/@MachineHours
')
SELECT @myDoc

-- delete an element
SET @myDoc.modify('
  delete /Root/Location/step[2]
')
SELECT @myDoc

-- delete text node (in <Location>
SET @myDoc.modify('
  delete /Root/Location/text()
')
SELECT @myDoc

-- delete all processing instructions
SET @myDoc.modify('
  delete //processing-instruction()
')
SELECT @myDoc

 

 

--B. 从存储在非类型化的 xml 列中的文档中删除节点

delete XML DML 语句从存储在列中的文档中删除 <Features> 的第二个子元素。
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

-- verify the contents before delete，query返回Features元素下的所有节点,以XML格式返回
SELECT x.query('//ProductDescription/Features')
FROM #T
-- delete the second feature
UPDATE #T
SET x.modify('delete /Root/ProductDescription/Features/*[2]')
-- verify the deletion
SELECT x.query(' //ProductDescription/Features')
FROM #T

DROP TABLE #T

 

C. 从非类型化的 xml 列中删除节点

create table #T(ProductModelID int primary key,
Instructions xml )
go
insert  #T
select ProductModelID, Instructions
from Production.ProductModel
where ProductModelID=7
go
select Instructions
from #T
--1) insert <Location 1000/>. Note: <Root> must be singleton in the query
update #T
set Instructions.modify('
  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
  insert <MI:Location LocationID="1000"  LaborHours="1000" >
           These are manu steps at location 1000.
           <MI:step>New step1 instructions</MI:step>
           Instructions for step 2 are here
           <MI:step>New step 2 instructions</MI:step>
         </MI:Location>
  as first
  into   (/MI:root)[1]
')
go
select Instructions
from #T

-- delete an XmlNode
update #T
set Instructions.modify('
  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
  delete(/MI:root/MI:Location[@LocationID=1000])
')
go
select Instructions
from #T

-- delete an attribute
update #T
set Instructions.modify('
  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
  delete(/MI:root/MI:Location[@LocationID=1000]/@LaborHours)
')
go
select Instructions
from #T
-- delete text in <location>
update #T
set Instructions.modify('
  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
  delete(/MI:root/MI:Location[@LocationID=1000]/text())
')
go
select Instructions
from #T
-- delete 2nd manu step at location 1000
update #T
set Instructions.modify('
  declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
  delete(/MI:root/MI:Location[@LocationID=1000]/MI:step[2])
')
go
select Instructions
from #T
-- cleanup
drop table #T
go