Updategram 简介 (SQLXML 4.0)Introduction to Updategrams (SQLXML 4.0)

适用于: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

您可以 MicrosoftMicrosoft SQL ServerSQL Server 通过使用 UPDATEGRAM 或 OPENXML 函数,从现有的 XML 文档中修改) 中的数据库 (插入、更新或删除 Transact-SQLTransact-SQLYou can modify (insert, update, or delete) a database in MicrosoftMicrosoft SQL ServerSQL Server from an existing XML document by using an updategram or the OPENXML Transact-SQLTransact-SQL function.

OPENXML 函数通过拆分现有 XML 文档并提供可以传递给 INSERT、UPDATE 或 DELETE 语句的行集来修改数据库。The OPENXML function modifies a database by shredding the existing XML document and providing a rowset that can be passed to an INSERT, UPDATE, or DELETE statement. 使用 OPENXML 时,直接针对数据库表进行操作。With OPENXML, operations are performed directly against the database tables. 因此,在行集提供程序(如表)可以显示为源时,最适合使用 OPENXML。Therefore, OPENXML is most appropriate wherever rowset providers, such as a table, can appear as a source.

与 OPENXML 一样,updategram 允许您在数据库中插入、更新或删除数据;不过,updategram 针对带批注的 XSD(或 XDR)架构提供的 XML 视图进行操作,例如将更新应用于映射架构提供的 XML 视图。Like OPENXML, an updategram allows you to insert, update, or delete data in the database; however, an updategram works against the XML views provided by the annotated XSD (or an XDR) schema; for example, the updates are applied to the XML view provided by the mapping schema. 而映射架构则具有将 XML 元素和属性映射到相应的数据库表和列所需的信息。The mapping schema, in turn, has the necessary information to map XML elements and attributes to the corresponding database tables and columns. updategram 使用此映射信息更新数据库表和列。The updategram uses this mapping information to update the database tables and columns.

备注

本文档假定您熟悉 SQL ServerSQL Server 中的模板和映射架构支持。This documentation assumes that you are familiar with templates and mapping schema support in SQL ServerSQL Server. 有关详细信息,请参阅 (SQLXML 4.0)中带批注的 XSD 架构简介 For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). 对于使用 XDR 的旧版应用程序,请参阅 SQLXML 4.0)中 (弃用的带批注 XDR 架构 For legacy applications that use XDR, see Annotated XDR Schemas (Deprecated in SQLXML 4.0).

Updategram 中必需的命名空间Required Namespaces in the Updategram

Updategram 中的关键字(如 <sync><before><after> )存在于 urn:架构-microsoft com: updategram 命名空间中。The keywords in an updategram, such as <sync>, <before>, and <after>, exist in the urn:schemas-microsoft-com:xml-updategram namespace. 您可以为该命名空间使用任意前缀。The namespace prefix that you use is arbitrary. 在本文档中, updg 前缀表示 updategram 命名空间。In this documentation, the updg prefix denotes the updategram namespace.

检查语法Reviewing Syntax

Updategram 是一个具有 <sync><before><after> 块的模板,它们构成了 updategram 的语法。An updategram is a template with <sync>, <before>, and <after> blocks that form the syntax of the updategram. 以下代码显示了此语法的最简单形式:The following code shows this syntax in its simplest form:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
  <updg:sync [mapping-schema= "AnnotatedSchemaFile.xml"] >  
    <updg:before>  
        ...  
    </updg:before>  
    <updg:after>  
        ...  
    </updg:after>  
  </updg:sync>  
</ROOT>  

以下定义描述了其中每个块的作用:The following definitions describe the role of each of these blocks:

<before>
标识记录实例的现有状态(也称为“以前状态”)。Identifies the existing state (also called "the before state") of the record instance.

<after>
标识要将数据更改到的新状态。Identifies the new state to which data is to be changed.

<sync>
包含 <before><after> 块。Contains the <before> and <after> blocks. <sync> 块可以包含多组 <before><after> 块。A <sync> block can contain more than one set of <before> and <after> blocks. 如果有多个 <before><after> 块,则这些块 (即使为空) 也必须指定为成对。If there is more than one set of <before> and <after> blocks, these blocks (even if they are empty) must be specified as pairs. 此外,updategram 可以有多个 <sync> 块。Furthermore, an updategram can have more than one <sync> block. 每个 <sync> 块都是一个事务单元 (这意味着块中的所有内容 <sync> 均已完成或未完成任何操作) 。Each <sync> block is one unit of transaction (which means that either everything in the <sync> block is done or nothing is done). 如果 <sync> 在 updategram 中指定多个块,则一个块的失败不 <sync> 会影响其他 <sync> 块。If you specify multiple <sync> blocks in an updategram, the failure of one <sync> block does not affect the other <sync> blocks.

Updategram 是删除、插入还是更新记录实例取决于 <before> 和块的内容 <after>Whether an updategram deletes, inserts, or updates a record instance depends on the contents of the <before> and <after> blocks:

  • 如果记录实例只出现在块中 <before> ,而在块中没有对应的实例 <after> ,则 updategram 将执行删除操作。If a record instance appears only in the <before> block with no corresponding instance in the <after> block, the updategram performs a delete operation.

  • 如果记录实例只出现在块中 <after> ,而在块中没有对应的实例 <before> ,则是插入操作。If a record instance appears only in the <after> block with no corresponding instance in the <before> block, it is an insert operation.

  • 如果记录实例出现在块中 <before> 并且在块中具有相应的实例 <after> ,则是更新操作。If a record instance appears in the <before> block and has a corresponding instance in the <after> block, it is an update operation. 在这种情况下,updategram 会将记录实例更新为在块中指定的值 <after>In this case, the updategram updates the record instance to the values that are specified in the <after> block.

在 Updategram 中指定映射架构Specifying a Mapping Schema in the Updategram

在 updategram 中,映射架构(支持 XSD 和 XDR 架构)所提供的 XML 抽象可以是隐式的,也可以是显式的(即无论是否指定映射架构,updategram 都可以工作)。In an updategram, the XML abstraction that is provided by a mapping schema (both XSD and XDR schemas are supported) can be implicit or explicit (that is, an updategram can work with or without a specified mapping schema). 如果未指定映射架构,则 updategram 将采用默认映射) (隐式映射,其中块或块中的每个元素都 <before> <after> 映射到表,每个元素的子元素或属性映射到数据库中的列。If you do not specify a mapping schema, the updategram assumes an implicit mapping (the default mapping), where each element in the <before> block or <after> block maps to a table and each element's child element or attribute maps to a column in the database. 如果显式指定映射架构,updategram 中的元素和属性必须与映射架构中的元素和属性匹配。If you explicitly specify a mapping schema, the elements and attributes in the updategram must match the elements and attributes in the mapping schema.

隐式(默认)映射Implicit (default) Mapping

在大多数情况下,执行简单更新的 updategram 可能不需要映射架构。In most cases, an updategram that performs simple updates might not require a mapping schema. 此时 updategram 依赖于默认映射架构。In this case, the updategram relies on the default mapping schema.

以下 updategram 演示隐式映射。The following updategram demonstrates implicit mapping. 在此示例中,updategram 在 Sales.Customer 表中插入一个新客户。In this example, the updategram inserts a new customer in the Sales.Customer table. 由于此 updategram 使用隐式映射,因此 <Sales.Customer> 元素映射到 customer 表,CustomerID 和 SalesPersonID 属性映射到 customer 表中的相应列。Because this updategram uses implicit mapping, the <Sales.Customer> element maps to the Sales.Customer table, and the CustomerID and SalesPersonID attributes map to the corresponding columns in the Sales.Customer table.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
<updg:sync >  
<updg:before>  
</updg:before>  
<updg:after>  
    <Sales.Customer CustomerID="1" SalesPersonID="277" />  
    </updg:after>  
</updg:sync>  
</ROOT>  

显式映射Explicit Mapping

如果指定映射架构(XSD 或 XDR),则 updategram 使用该架构确定要更新的数据库表和列。If you specify a mapping schema (either XSD or XDR), the updategram uses the schema to determine the database tables and columns that are to be updated.

如果 updategram 执行复杂更新 (例如,基于映射架构) 中指定的父子关系在多个表中插入记录,则必须通过使用 updategram 执行的 映射架构 特性显式提供映射架构。If the updategram performs a complex update (for example, inserting records in multiple tables on the basis of the parent-child relationship that is specified in the mapping schema), you must explicitly provide the mapping schema by using the mapping-schema attribute against which the updategram executes.

由于 updategram 是模板,因此为 updategram 中的映射架构指定的路径是相对于模板文件的位置而言(即相对于存储 updategram 的位置而言)。Because an updategram is a template, the path specified for the mapping schema in the updategram is relative to the location of the template file (relative to where the updategram is stored). 有关详细信息,请参阅 在 Updategram 中指定带批注的映射架构 (SQLXML 4.0)For more information, see Specifying an Annotated Mapping Schema in an Updategram (SQLXML 4.0).

Updategram 中以元素为中心的映射和以属性为中心的映射Element-centric and Attribute-centric Mapping in Updategrams

使用默认映射(在 updategram 中未指定映射架构)时,如果是以元素为中心的映射,则 updategram 元素映射到表并且子元素映射到列。如果是以属性为中心的映射,则属性映射到列。With default mapping (when the mapping schema is not specified in the updategram), the updategram elements map to tables and the child elements (in the case of element-centric mapping) and the attributes (in the case of attribute-centric mapping) map to columns.

以元素为中心的映射Element-centric Mapping

在以元素为中心的 updategram 中,元素包含指示元素属性的子元素。In an element-centric updategram, an element contains child elements that denote the properties of the element. 请参阅以下 updategram 示例。As an example, refer to the following updategram. <Person.Contact> 元素包含 <FirstName><LastName> 子元素。The <Person.Contact> element contains the <FirstName> and <LastName> child elements. 这些子元素是元素的属性 <Person.Contact>These child elements are properties of the <Person.Contact> element.

由于此 updategram 未指定映射架构,因此 updategram 将使用隐式映射,其中 <Person.Contact> 元素映射到 Person 表,而其子元素映射到 FirstName 和 LastName 列。Because this updategram does not specify a mapping schema, the updategram uses implicit mapping, where the <Person.Contact> element maps to the Person.Contact table and its child elements map to the FirstName and LastName columns.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
<updg:sync >  
  <updg:after>  
    <Person.Contact>  
       <FirstName>Catherine</FirstName>  
       <LastName>Abel</LastName>  
    </Person.Contact>  
  </updg:after>  
</updg:sync>  
</ROOT>  

以属性为中心的映射Attribute-centric Mapping

在以属性为中心的映射中,元素具有属性。In an attribute-centric mapping, the elements have attributes. 以下 updategram 使用以属性为中心的映射。The following updategram uses attribute-centric mapping. 在此示例中, <Person.Contact> 元素包含 FirstNameLastName 属性。In this example, the <Person.Contact> element consists of the FirstName and LastName attributes. 这些属性是元素的属性 <Person.Contact>These attributes are the properties of the <Person.Contact> element. 如前面的示例所示,此 updategram 不指定任何映射架构,因此它依赖于隐式映射来将元素映射到 <Person.Contact> 表中的相应列。As in the previous example, this updategram specifies no mapping schema, so it relies on implicit mapping to map the <Person.Contact> element to the Person.Contact table and the element's attributes to the respective columns in the table.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
<updg:sync >  
  <updg:before>  
  </updg:before>  
  <updg:after>  
    <Person.Contact FirstName="Catherine" LastName="Abel" />  
  </updg:after>  
</updg:sync>  
</ROOT>  

同时使用以元素为中心的映射和以属性为中心的映射Using Both Element-centric and Attribute-centric Mapping

可以组合使用以元素为中心的映射和以属性为中心的映射,如以下 updategram 中所示。You can specify a mix of element-centric and attribute-centric mapping, as shown in the following updategram. 请注意, <Person.Contact> 元素同时包含特性和子元素。Notice that the <Person.Contact> element contains both an attribute and a child element. 此 updategram 也依赖于隐式映射。Also, this updategram relies on implicit mapping. 因此, FirstName 属性和 <LastName> 子元素映射到 Person 表中的相应列。Thus, the FirstName attribute and the <LastName> child element map to corresponding columns in the Person.Contact table.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">  
<updg:sync >  
  <updg:before>  
  </updg:before>  
  <updg:after>  
    <Person.Contact FirstName="Catherine" >  
       <LastName>Abel</LastName>  
    </Person.Contact>  
  </updg:after>  
</updg:sync>  
</ROOT>  

使用在 SQL Server 中有效但在 XML 中无效的字符Working with Characters Valid in SQL Server but Not Valid in XML

SQL ServerSQL Server 中,表名称可以包含空格。In SQL ServerSQL Server, table names can include a space. 但是,此类表名称在 XML 中无效。However, this type of table name is not valid in XML.

若要对是有效 SQL ServerSQL Server 标识符,但不是有效 XML 标识符的字符进行编码,请使用 "__xHHHH _ _ " 作为编码值,其中 HHHH 代表最高有效位第一次的字符的四位十六进制 UCS-2 代码。To encode characters that are valid SQL ServerSQL Server identifiers but are not valid XML identifiers, use '__xHHHH__' as the encoding value, where HHHH stands for the four-digit hexadecimal UCS-2 code for the character in the most significant bit-first order. 使用此编码方案时,空格字符将被替换为一个空格字符的四位数十六进制代码) 的 (x0020;因此,中的表名 [Order Details] 在 SQL ServerSQL Server _ XML 中变为 _x005B_Order_x0020_Details_x005D。Using this encoding scheme, a space character gets replaced with x0020 (the four-digit hexadecimal code for a space character); thus, the table name [Order Details] in SQL ServerSQL Server becomes _x005B_Order_x0020_Details_x005D_ in XML.

同样,你可能需要指定由三个部分组成的元素名称,如 <[database].[owner].[table]> 。Similarly, you might need to specify three-part element names, such as <[database].[owner].[table]>. 由于方括号 ( [and] ) 在 XML 中无效,因此您必须将其指定为 <_x005B_database_x005D_._x005B_owner_x005D_._x005B_table_x005D_> ,其中 _x005B _ 是左括号的编码 ( [) ,_x005D _ 是右大括号的编码 (] ) 。Because the bracket characters ([ and ]) are not valid in XML, you must specify this as <_x005B_database_x005D_._x005B_owner_x005D_._x005B_table_x005D_>, where _x005B_ is the encoding for the left bracket ([) and _x005D_ is the encoding for the right bracket (]).

执行 UpdategramExecuting Updategrams

由于 updategram 是模板,因此模板的所有处理机制均适用于 updategram。Because an updategram is a template, all the processing mechanisms of a template apply to the updategram. 对于 SQLXML 4.0,可以通过以下方式之一来执行 updategram:For SQLXML 4.0, you can execute an updategram in either of the following ways:

  • 在 ADO 命令中提交它。By submitting it in an ADO command.

  • 将其作为 OLE DB 命令提交。By submitting it as an OLE DB command.

另请参阅See Also

(SQLXML 4.0)的 Updategram 安全注意事项 Updategram Security Considerations (SQLXML 4.0)