SQL Server 与简单地XML操作

SQL Server 与简单地XML操作

/****将元素节点插入文档中*******/
DECLARE @myDoc xml;         
SET @myDoc = '<Root>         
    <ProductDescription ProductID="1" ProductName="Road Bike">         
        <Features>         
        </Features>         
    </ProductDescription>         
</Root>'  ;       
SELECT @myDoc;  
   
SET @myDoc.modify('         
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>   
into (/Root/ProductDescription/Features)[1]') ;  
SELECT @myDoc ;        

SET @myDoc.modify('         
insert <Warranty>1 year parts and labor</Warranty>          
as first         
into (/Root/ProductDescription/Features)[1]         
')  ;       
SELECT @myDoc  ;       
        
SET @myDoc.modify('         
insert <Material>Aluminium</Material>          
as last         
into (/Root/ProductDescription/Features)[1]         
')         
SELECT @myDoc ;        
      
set @myDoc.modify('         
insert <BikeFrame>Strong long lasting</BikeFrame>   
after (/Root/ProductDescription/Features/Material)[1]         
')  ;       
SELECT @myDoc;  

/****从存储在非类型化的 xml 变量中的文档中删除节点*********/
DECLARE @myDoc1 xml  
SET @myDoc1 = '<?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 @myDoc1  
  
SET @myDoc1.modify('  
  delete /Root/Location/@MachineHours  
')  
SELECT @myDoc1  
  
SET @myDoc1.modify('  
  delete /Root/Location/step[1]  
')  
SELECT @myDoc1  
  
SET @myDoc1.modify('  
  delete /Root/Location/text()  
')  
SELECT @myDoc1  
  
SET @myDoc1.modify('  
  delete //processing-instruction()  
')  
SELECT @myDoc1  

/****在 XML 实例中替换值**********/
DECLARE @myDoc2 xml;  
SET @myDoc2 = '<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 @myDoc2;  
  
SET @myDoc2.modify('  
  replace value of (/Root/Location/step[1]/text())[1]  
  with     "new text describing the manu step"  
');  
SELECT @myDoc2;  

SET @myDoc2.modify('  
  replace value of (/Root/Location/@LaborHours)[1]  
  with     "100.0"  
');  
SELECT @myDoc2;  

/****针对 xml 类型的变量使用 nodes() 方法*******/
DECLARE @x xml  
SET @x='<Root>  
    <row><name>Larry</name></row>  
    <row><name>moe</name></row>  
    <row/>  
</Root>'  
SELECT T.col.query('.') AS result  
FROM   @x.nodes('/Root/row') T(col)  

详细了解参考链接：https://docs.microsoft.com/zh-cn/sql/t-sql/xml/xml-data-modification-language-xml-dml?view=sql-server-2017