插入 (XML DML)insert (XML DML)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

将 Expression1 标识的一个或多个节点作为 Expression2 标识的节点的子节点或同级节点插入 。Inserts one or more nodes identified by Expression1 as child nodes or siblings of the node identified by Expression2.

语法Syntax

insert Expression1 (  
{AS first | AS last} INTO | AFTER | BEFORE  
Expression2  
)  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

Expression1Expression1
标识要插入的一个或多个节点。Identifies one or more nodes to insert. 这可以是一个常量 XML 实例、对应用修改方法的相同 XML 架构集合的类型化 XML 数据类型实例的引用、使用单独的 sql:column()/sql:variable() 函数的非类型化 XML 数据类型实例或者是一个 XQuery 表达式 。This can be a constant XML instance; a reference to a typed XML data type instance of the same XML Schema collection on which the modify method is being applied; an untyped XML data type instance using a stand-alone sql:column()/sql:variable() function; or an XQuery expression. 该表达式可以得出节点、文本节点或一组有序的节点。The expression can result in a node, and also a text node, or in an ordered sequence of nodes. 但它无法解得根 (/) 节点。It cannot resolve to the root (/) node. 如果该表达式得出一个值或一组值,则这些值作为单个文本节点插入,各值之间以空格分隔开。If the expression results in a value or a sequence of values, the values are inserted as a single text node with a space separating each value in the sequence. 如果将多个节点指定为常量,则这些节点用括号括住,并以逗号分隔开。If you specify multiple nodes as constant, the nodes are included in parentheses and are separated by commas. 但无法插入异构序列(如一组元素、属性或值)。You cannot insert heterogeneous sequences such as a sequence of elements, attributes, or values. 如果 Expression1 解析到一个空序列,不发生插入操作且不返回任何错误。If Expression1 resolves to an empty sequence, no insertion occurs and no errors are returned.

更改为into
Expression1 标识的节点作为 Expression2 标识的节点的直接后代(子节点)插入 。Nodes identified by Expression1 are inserted as direct descendents (child nodes) of the node identified by Expression2. 如果 Expression2 中的节点已有一个或多个子节点,则必须使用 as first 或 as last 来指定所需的新节点添加位置 。If the node in Expression2 already has one or more child nodes, you must use either as first or as last to specify where you want the new node added. 例如,分别在子列表的开头或末尾。For example, at the start or at the end of the child list, respectively. 插入属性时忽略 as first 和 as last 关键字 。The as first and as last keywords are ignored when attributes are inserted.

afterafter
Expression1 标识的节点作为 Expression2 标识的节点的同级节点直接在其后面插入 。Nodes identified by Expression1 are inserted as siblings directly after the node identified by Expression2. after 关键字不能用于插入属性。The after keyword cannot be used to insert attributes. 例如,它不能用于插入属性构造函数或从 XQuery 返回属性。For example, it cannot be used to insert an attribute constructor or to return an attribute from an XQuery.

beforebefore
Expression1 标识的节点作为 Expression2 标识的节点的同级节点直接在其前面插入 。Nodes identified by Expression1 are inserted as siblings directly before the node identified by Expression2. before 关键字不能用于插入属性。The before keyword cannot be used when attributes are being inserted. 例如,它不能用于插入属性构造函数或从 XQuery 返回属性。For example, it cannot be used to insert an attribute constructor or to return an attribute from an XQuery.

Expression2Expression2
标识节点。Identifies a node. Expression1 标识的节点是相对于 Expression2 标识的节点插入的 。The nodes identified in Expression1 are inserted relative to the node identified by Expression2. 这可以是 XQuery 表达式,返回当前被引用的文档中现有节点的引用。This can be an XQuery expression that returns a reference to a node that exists in the currently referenced document. 如果返回多个节点,则插入失败。If more than one node is returned, the insert fails. 如果 Expression2 返回一个空序列,不发生插入操作且不返回任何错误。If Expression2 returns an empty sequence, no insertion occurs and no errors are returned. 如果 Expression2 在静态时不是单一实例,将返回静态错误。If Expression2 is statically not a singleton, a static error is returned. Expression2 不能为处理指令、注释或属性。Expression2 cannot be a processing instruction, comment, or attribute. 请注意,Expression2 必须是文档中现有节点的引用,而不是构造的节点。Note that Expression2 must be a reference to an existing node in the document and not a constructed node.

示例Examples

A.A. 将元素节点插入文档中Inserting element nodes into the document

以下示例说明了如何将元素插入文档中。The following example illustrates how to insert elements into a document. 首先,将 XML 文档分配给 xml 类型的变量。First, an XML document is assigned to a variable of xml type. 然后,通过几个 insert XML DML 语句,该示例说明如何将元素节点插入文档中。Then, through several insert XML DML statements, the example illustrates how element nodes are inserted in the document. 每次插入后,SELECT 语句都会显示结果。After each insert, the SELECT statement displays the result.

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  

请注意,此示例中的各种路径表达式都指定“[1]”以要求每次只返回单个目标。Note that various path expressions in this example specify "[1]" as a per-static typing requirement. 这样就确保了只有单个目标节点。This ensures a single target node.

B.B. 将多个元素插入文档中Inserting multiple elements into the document

在以下示例中,首先将文档分配给 xml 类型的变量。In the following example, a document is first assigned to a variable of xml type. 然后,将包括两个元素(代表产品功能)的序列分配给第二个 xml 类型的变量。Then, a sequence of two elements, representing product features, is assigned to a second variable of xml type. 再将此序列插入第一个变量。This sequence is then inserted into the first variable.

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  

C.C. 将属性插入文档中Inserting attributes into a document

以下示例说明了如何将属性插入文档中。The following example illustrates how attributes are inserted in a document. 首先,将文档分配给 xml 类型变量。First, a document is assigned to an xml type variable. 然后,使用一系列 insert XML DML 语句将属性插入文档中。Then, a series of insert XML DML statements is used to insert attributes into the document. 每次插入属性后,SELECT 语句都会显示结果。After each attribute insertion, the SELECT statement displays the result.

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  

D.D. 插入注释节点Inserting a comment node

在此查询中,首先将 XML 文档分配给 xml 类型的变量。In this query, an XML document is first assigned to a variable of xml type. 然后,使用 XML DML 将注释节点插入到第一个 <step> 元素后。Then, XML DML is used to insert a comment node after the first <step> element.

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  

E.E. 插入处理指令Inserting a processing instruction

在以下查询中,首先将 XML 文档分配给 xml 类型的变量。In the following query, an XML document is first assigned to a variable of xml type. 然后,使用 XML DML 关键字将处理指令插入文档的开头。Then, the XML DML keyword is used to insert a processing instruction at the start of the 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 ;  
SET @myDoc.modify('   
insert <?Program = "Instructions.exe" ?>   
before (/Root)[1] ') ;  
SELECT @myDoc ;  
GO  

F.F. 使用 CDATA 部分插入数据Inserting data using a CDATA section

插入文本时,如果该文本包含在 XML 中无效的字符(如 < 或 >),可以使用 CDATA 部分插入数据,如以下查询中所示。When you insert text that includes characters that are not valid in XML, such as < or >, you can use CDATA sections to insert the data as shown in the following query. 该查询指定一个 CDATA 部分,但该部分作为文本节点添加进来,其中的无效字符转换成实体。The query specifies a CDATA section, but it is added as a text node with any invalid characters converted to entities. 例如,< 另存为 &lt;For example, < is saved as &lt;.

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  

查询将文本节点插入 <Features> 元素中:The query inserts a text node into the <Features> element:

<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features> &lt;notxml@gt; as text &lt;/notxml&gt; or cdata </Features>  
</ProductDescription>  
</Root>       

G.G. 插入文本节点Inserting text node

在此查询中,首先将 XML 文档分配给 xml 类型的变量。In this query, an XML document is first assigned to a variable of xml type. 然后,使用 XML DML 将文本节点插入为 <Root> 元素的第一个子元素。Then, XML DML is used to insert a text node as the first child of the <Root> element. 并使用文本构造函数指定文本。The text constructor is used to specify the text.

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;  

H.H. 将新元素插入非类型化的 xml 列Inserting a new element into an untyped xml column

以下示例应用 XML DML 来更新 xml 类型列中存储的 XML 实例:The following example applies XML DML to update an XML instance stored in an xml type 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  

同样,插入 <Material> 元素节点时,路径表达式必须返回单个目标。Again, when the <Material> element node is inserted, the path expression must return a single target. 这通过在表达式结尾处添加 [1] 来显式指定。This is explicitly specified by adding a [1] at the end of the expression.

-- check the update  
SELECT x.query(' //ProductDescription/Features')  
FROM T;  
GO  

I.I. 根据 if 条件语句进行插入Inserting based on an if condition statement

在以下示例中,将 IF 条件语句指定为 insert XML DML 语句中 Expression1 的一部分。In the following example, an IF condition is specified as part of Expression1 in the insert XML DML statement. 如果条件为 True,则将属性添加到 <WorkCenter> 元素中。If the condition is True, an attribute is added to the <WorkCenter> element.

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  

以下示例与此类似,除了在条件为 True 时 insert XML DML 语句会在文档中插入元素。The following example is similar, except that the insert XML DML statement inserts an element in the document if the condition is True. 也就是说,如果 <WorkCenter> 元素的 <step> 子元素少于或等于两个,则在文档中插入元素。That is, if the <WorkCenter> element has less than or is equal to two <step> child elements.

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 (count(/Root/Location/step) <= 2)  
then element step { "This is a new step" }  
else ()  
    as last into   (/Root/Location[@LocationID=10])[1] ');  
SELECT @myDoc;  
GO  

结果如下:This is the result:

<Root>  
 <WorkCenter WorkCenterID="10" LaborHours="1.2">  
  <step>Manufacturing step 1 at this work center</step>  
  <step>Manufacturing step 2 at this work center</step>  
  <step>This is a new step</step>  
 </WorkCenter>  

J.J. 将节点插入类型化的 xml 列中Inserting nodes in a typed xml column

此示例将元素和属性插入在类型化的 xml 列存储的生产说明 XML 中。This example inserts an element and an attribute into a manufacturing instructions XML stored in a typed xml column.

在该示例中,首先在 AdventureWorks 数据库中创建带有类型化的 xml 列的表 (T)。In the example, you first create a table (T) with a typed xml column, in the AdventureWorks database. 然后将一个生产说明 XML 实例从 ProductModel 表的 Instructions 列复制到表 T 中。随后再对表 T 中的 XML 内容应用插入操作。You then copy a manufacturing instructions XML instance from the Instructions column in the ProductModel table into table T. Insertions are then applied to XML in table T.

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             

另请参阅See Also

类型化的 XML 与非类型化的 XML 的比较 Compare Typed XML to Untyped XML
创建 XML 数据的实例 Create Instances of XML Data
xml 数据类型方法 xml Data Type Methods
XML 数据修改语言 (XML DML)XML Data Modification Language (XML DML)