Using Updategrams to Modify Data
You can modify (insert, update, or delete) a database in Microsoft® SQL Server™ 2000 from an existing XML document by using an updategram or the OPENXML Transact-SQL function.
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 to perform the necessary operation directly against the database tables. Therefore, using OPENXML is most appropriate wherever rowset providers, such as a table, can appear as a source.
Like OPENXML, an updategram allows you to insert, update, or delete data in the database; however, an updategram works against the XML views that are provided by the annotated XSD (or an XDR) schema (for example, the updates are applied to the XML view provided by the mapping schema). The mapping schema, in turn, has the necessary information to map XML elements and attributes to the corresponding database tables and columns. The updategram uses this mapping information to update the database tables and columns.
This documentation assumes that you are familiar with templates and mapping schema support in SQL Server 2000. For more information about templates, see "Executing Template Files Using HTTP" in SQL Server Books Online. For more information about mapping schemas, see "Creating XML Views Using Annotated XDR Schemas" in SQL Server Books Online.
Required Namespaces in the 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. In this documentation, the updg prefix denotes the updategram namespace.
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:
Identifies the existing state (also referred to as "the before state") of the record instance.
Identifies the new state to which data is to be changed.
Contains the <before> and <after> blocks. A <sync> block can contain more than one set of <before> and <after> blocks. If there is more than one set of <before> and <after> blocks, these blocks (even if they are empty) must be specified as pairs. Furthermore, an updategram can have more than one <sync> block. Each <sync> block is one unit of transaction (which means that either everything in the <sync> block is done or nothing is done). If you specify multiple <sync> blocks in an updategram, the failure of one <sync> block does not affect the other <sync> blocks.
Whether an updategram deletes, inserts, or updates a record instance depends on the contents of the <before> and <after> blocks:
- If a record instance appears only in the <before> block with no corresponding instance in the <after> block, the updategram performs a delete operation.
- If a record instance appears only in the <after> block with no corresponding instance in the <before> block, it is an insert operation.
- If a record instance appears in the <before> block and has a corresponding instance in the <after> block, it is an update operation. In this case, the updategram updates the record instance to the values that are specified in the <after> block.
Specifying a Mapping Schema in the 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). 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 subelement or attribute maps to a column in the database. 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
In most cases, an updategram that performs simple updates might not require a mapping schema. In this case, the updategram relies on the default mapping schema.
The following updategram demonstrates implicit mapping. In this example, the updategram inserts a new customer in the Customers table. Because this updategram uses implicit mapping, the <Customers> element maps to the Customers table, and the CustomerID and CompanyName attributes map to the corresponding columns in the Customers table.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync > <updg:before> </updg:before> <updg:after> <Customers CustomerID="AAAAA" CompanyName="Bottom-Dollar Markets" /> </updg:after> </updg:sync> </ROOT>
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.
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.
Because an updategram is a template, the path that is specified for the mapping schema in the updategram is relative to the location of the template file (relative to where the updategram is stored). For more information, see Specifying an Annotated Schema in an Updategram.
Element-centric and Attribute-centric Mapping in Updategrams
With default mapping (when the mapping schema is not specified in the updategram), the updategram elements map to tables and the subelements (in case of element-centric mapping) and the attributes (in case of attribute-centric mapping) map to columns.
In an element-centric updategram, an element contains subelements that denote the properties of the element. As an example, refer to the following updategram. The <Employees> element contains the <FirstName>and <LastName> subelements. These subelements are properties of the <Employees> element.
Because this updategram does not specify a mapping schema, the updategram uses implicit mapping, where the <Employees> element maps to the Employees table and its subelements map to the FirstName and LastName columns.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync > <updg:after> <Employees> <FirstName>Nancy</FirstName> <LastName>Davolio</LastName> </Employees> </updg:after> </updg:sync> </ROOT>
In an attribute-centric mapping, the elements have attributes. The following updategram uses attribute-centric mapping. In this example, the <Employees> element consists of the FirstName and LastName attributes. These attributes are the properties of the <Employees> element. Once again, this updategram specifies no mapping schema, so it relies on implicit mapping to map the <Employees> element to the Employees 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> <Employees FirstName="Nancy" LastName="Davolio" /> </updg:after> </updg:sync> </ROOT>
Using Both Element-centric and Attribute-centric Mapping
You can specify a mix of element-centric and attribute-centric mapping, as shown in the following updategram. Notice that the <Employees> element contains both an attribute and a subelement. Also, this updategram relies on implicit mapping. Thus, the FirstName attribute and the LastName subelement map to corresponding columns in the Employees table.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync > <updg:before> </updg:before> <updg:after> <Employees FirstName="Nancy" > <LastName>Davolio</LastName> </Employees> </updg:after> </updg:sync> </ROOT>
Dealing with Characters That Are Valid in SQL Server but Not Valid in XML
In SQL Server, table names can include a space, such as the Order Details table in the Northwind database. However, this type of table name is not valid in XML.
To encode characters that are valid SQL Server identifiers but that 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. 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 Server becomes _x005B_Order_x0020_Details_x005D_ in XML.
Another example of this is specifying three-part element names, such as <[database].[owner].[table]>. 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 (]).
Because an updategram is a template, all the processing mechanisms of a template apply to the updategram. You can:
- Post an updategram by using HTTP.
- Save it in a file and execute in the URL as a template file.
- Submit it with an ADO or OLE DB command.
Updategram and DiffGram Security Issues