Inserting element nodes into the document - TSQL XML insert (XML DML)

Tsql теория > Inserting element nodes into the document - TSQL XML insert (XML DML)
12.01.2019 14:15:14



Статья:

DECLARE @myDoc xml; 
 
SET @myDoc = ''<pins>
  <pin>
    <sABS_Code>SalesLogix</sABS_Code>
    <sPin>Q0CEWA1</sPin>
    <bMain>true</bMain>
  </pin>
</pins>''
 
SET @myDoc.modify(''         
insert <common>
<idSubject></idSubject>
</common> 
as first 
into (/)'') ;  
SELECT @myDoc ;   
---------------------------------------------
Inserting element nodes into the document
USE AdventureWorks;  
GO  
DECLARE @myDoc xml;         
SET @myDoc = ''<Root>         
    <ProductDescription ProductID="1" ProductName="Road Bike">         
        <Features>         
        </Features>         
    </ProductDescription>         
</Root>''  ;       
SELECT @myDoc;     
-- insert first feature child (no need to specify as first or as last)         
SET @myDoc.modify(''         
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>   
into (/Root/ProductDescription/Features)[1]'') ;  
SELECT @myDoc ;        
-- insert second feature. We want this to be the first in sequence so use ''as first''         
set @myDoc.modify(''         
insert <Warranty>1 year parts and labor</Warranty>          
as first         
into (/Root/ProductDescription/Features)[1]         
'')  ;       
SELECT @myDoc  ;       
-- insert third feature child. This one is the last child of <Features> so use ''as last''         
SELECT @myDoc         
SET @myDoc.modify(''         
insert <Material>Aluminium</Material>          
as last         
into (/Root/ProductDescription/Features)[1]         
'')         
SELECT @myDoc ;        
-- Add fourth feature - this time as a sibling (and not a child)         
-- ''after'' keyword is used (instead of as first or as last child)         
SELECT @myDoc  ;       
set @myDoc.modify(''         
insert <BikeFrame>Strong long lasting</BikeFrame>   
after (/Root/ProductDescription/Features/Material)[1]         
'')  ;       
SELECT @myDoc;  
GO
 
------------------------
Inserting multiple elements into the document
 
USE AdventureWorks;  
GO  
DECLARE @myDoc xml;  
SET @myDoc = N''<Root>             
<ProductDescription ProductID="1" ProductName="Road Bike">             
    <Features> </Features>             
</ProductDescription>             
</Root>'';  
DECLARE @newFeatures xml;  
SET @newFeatures = N''<Warranty>1 year parts and labor</Warranty>            
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>'';           
-- insert new features from specified variable            
SET @myDoc.modify(''             
insert sql:variable("@newFeatures")             
into (/Root/ProductDescription/Features)[1] '')             
SELECT @myDoc;  
GO  
 
------------------------
Inserting attributes into a document
USE AdventureWorks;  
GO  
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>'' ;  
SELECT @myDoc  ;          
-- insert LaborHours attribute             
SET @myDoc.modify(''             
insert attribute LaborHours {".5" }             
into (/Root/Location[@LocationID=10])[1] '') ;           
SELECT @myDoc  ;          
-- insert MachineHours attribute but its value is retrived from a sql variable @Hrs             
DECLARE @Hrs float ;            
SET @Hrs =.2   ;          
SET @myDoc.modify(''             
insert attribute MachineHours {sql:variable("@Hrs") }             
into   (/Root/Location[@LocationID=10])[1] '');            
SELECT @myDoc;             
-- insert sequence of attribute nodes (note the use of '','' and ()              
-- around the attributes.             
SET @myDoc.modify(''             
insert (              
           attribute SetupHours {".5" },             
           attribute SomeOtherAtt {".2"}             
        )             
into (/Root/Location[@LocationID=10])[1] '');             
SELECT @myDoc;  
GO  
 
---------------------
Inserting a comment node
USE AdventureWorks;  
GO  
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>'' ;           
SELECT @myDoc;             
SET @myDoc.modify(''             
insert <!-- some comment -->             
after (/Root/Location[@LocationID=10]/step[1])[1] '');            
SELECT @myDoc;  
GO  
 
--------------------------
Inserting a processing instruction
USE AdventureWorks;  
GO  
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>'' ;  
SELECT @myDoc ;  
SET @myDoc.modify(''   
insert <?Program = "Instructions.exe" ?>   
before (/Root)[1] '') ;  
SELECT @myDoc ;  
GO  
 
------------------------
Inserting data using a CDATA section
USE AdventureWorks;  
GO  
DECLARE @myDoc xml;             
SET @myDoc =   
''<Root>             
    <ProductDescription ProductID="1" ProductName="Road Bike">             
        <Features> </Features>             
    </ProductDescription>             
</Root>'' ;            
SELECT @myDoc ;            
SET @myDoc.modify(''             
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>   
into  (/Root/ProductDescription/Features)[1] '') ;   
SELECT @myDoc ;  
GO  
 
-----------------------
Inserting text node
USE AdventureWorks;  
GO  
DECLARE @myDoc xml;  
SET @myDoc = ''<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
 
</Features>  
</ProductDescription>  
</Root>''  
SELECT @myDoc;  
set @myDoc.modify(''  
 insert text{"Product Catalog Description"}   
 as first into (/Root)[1]  
'');  
SELECT @myDoc;  
 
----------------------
Inserting a new element into an untyped xml column
USE AdventureWorks;  
GO  
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  
-- insert a new element  
UPDATE T  
SET x.modify(''insert <Material>Aluminium</Material> as first  
  into   (/Root/ProductDescription/Features)[1]  
'');  
GO  
 
----------------
Inserting based on an if condition statement
USE AdventureWorks;  
GO  
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>'';  
SELECT @myDoc  
SET @myDoc.modify(''  
insert  
if (/Root/Location[@LocationID=10])  
then attribute MachineHours {".5"}  
else ()  
    as first into   (/Root/Location[@LocationID=10])[1] '');  
SELECT @myDoc;  
GO  
 
---------------
Inserting nodes in a typed xml column
USE AdventureWorks;  
GO            
DROP TABLE T;  
GO             
CREATE TABLE T(ProductModelID int primary key,    
Instructions xml (Production.ManuInstructionsSchemaCollection));  
GO  
INSERT T              
    SELECT ProductModelID, Instructions             
    FROM Production.ProductModel             
    WHERE ProductModelID=7;  
GO             
SELECT Instructions             
FROM T;  
-- now insertion begins             
--1) insert a new manu. Location. The <Root> specified as              
-- expression 2 in the insert() must be singleton.      
UPDATE T   
set Instructions.modify(''   
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";   
insert <MI:Location LocationID="1000" >   
           <MI:step>New instructions go here</MI:step>   
         </MI:Location>   
as first   
into   (/MI:root)[1]   
'') ;  
 
SELECT Instructions             
FROM T ;  
-- 2) insert attributes in the new <Location>             
UPDATE T             
SET Instructions.modify(''             
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";             
insert attribute LaborHours { "1000" }             
into (/MI:root/MI:Location[@LocationID=1000])[1] '');   
GO             
SELECT Instructions             
FROM T ;  
GO             
--cleanup             
DROP TABLE T ;  
GO