Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2000
SQLXML
 Inserting Data by Using XML Updateg...
SQLXML 3.0 Service Pack 3
Inserting Data by Using XML Updategrams

An updategram indicates an insert operation when a record instance appears in the <after> block but not in the corresponding <before> block. In this case, the updategram inserts the record in the <after> block into the database.

This is the updategram format for an insert operation:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync [mapping-schema="SampleSchema.xml"]  >
   [<updg:before>
   </updg:before>]
    <updg:after [updg:returnid="x y ...] >
       <ElementName [updg:id="value"] 
 [updg:at-identity="x"] 
 [updg:guid="y"]
 attribute="value" 
                   attribute="value"
                   ...
 />
 [<ElementName .../>... ]
 </updg:after>
 </updg:sync>
</ROOT>
<before> Block

The <before> block can be omitted for an insert operation. If the optional mapping-schema attribute is not specified, the <ElementName> that is specified in the updategram maps to a database table and the subelements or attributes map to columns in the table.

<after> Block

You can specify one or more records in the <after> block.

If the <after> block does not supply a value for a particular column, the updategram uses the default value that is specified in the annotated schema (if a schema has been specified). If the schema does not specify a default value for the column, the updategram does not specify any explicit value to this column and, instead, assigns the Microsoft® SQL Server™ default value (if specified) to this column. If there is no SQL Server default value and the column accepts a NULL value, the updategram sets the column value to NULL. If the column neither has a default value nor accepts a null value, the command fails and the updategram returns an error. The optional updg:returnid attribute is used to return the identity value that is generated by the system when a record is added in a table with an IDENTITY-type column.

updg:id Attribute

If the updategram is inserting only records, the updategram does not require the updg:id attribute. For more information about updg:id, see Updating Data by Using XML Updategrams.

updg:at-identity Attribute

When updategram inserts a record in a table that has an IDENTITY-type column, the updategram can capture the system assigned value by using the optional updg:at-identity attribute. The updategram can then use this value in subsequent operations. Upon execution of the updategram, you can return the identity value that is generated by specifying the updg:returnid attribute.

updg:guid Attribute

The updg:guid attribute is an optional attribute that generates a globally unique identifier. This value remains in scope for the entire <sync> block in which it is specified. You can use this value anywhere in the <sync> block. The attribute calls the NEWGUID() SQL Server function to generate the unique identifier.

Examples

Before using the updategram examples, note the following:

  • Most of the examples use default mapping (that is, no mapping schema is specified in the updategram). For more examples of updategrams that use mapping schemas, see Specifying an Annotated Mapping Schema in an Updategram.

  • Most of the examples use the Northwind sample database. All the updates are applied to the tables in this database. You can restore the Northwind database. For information about restoring the Northwind database, see "Northwind Sample Database" in SQL Server Books Online.

  • To test these examples of updategrams, you must create a virtual directory. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

  • Because an updategram is a template, you must store these updategram examples in the folder that is associated with the virtual name of template type.
A. Inserting a record by using an updategram

This attribute-centric updategram inserts a record in the Employees table in the Northwind database.

In this example, the updategram does not specify a mapping schema. Therefore, the updategram uses default mapping, in which the element name maps to a table name and the attributes or subelements map to columns in that table.

For the sake of simplicity, the updategram specifies only the column values that are required (FirstName and LastName). The EmployeeID is an IDENTITY-type column in the Employees table. Therefore, no values are specified for the EmployeeID column.

<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>

To test the updategram

Save the updategram (MyUpdategram.xml) in the folder that is associated with the virtual name of template type, and then execute the updategram by using the following URL:

http://IISServer/VirtualRoot/TemplateVirtualName/MyUpdategram.xml

In an element-centric mapping, the updategram looks like this:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
  <updg:before>
  </updg:before>
  <updg:after>
      <Employees>
          <FirstName>Nancy</FirstName>
         <LastName>Davolio</LastName>
      </Employees>
  </updg:after>
</updg:sync>
</ROOT>

In a mixed-mode (element-centric and attribute-centric) updategram, an element can have both attributes and subelements, as shown in this updategram:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
   <updg:before>
   </updg:before>
   <updg:after>
       <Employees FirstName="Nancy" >
            <LastName>Davoliio</LastName>
       </Employees>
  </updg:after>
</updg:sync>
</ROOT>
B. Inserting multiple records by using an updategram

This updategram adds two employee records to the Employees table. The updategram does not specify the optional <before> block.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync>
    <updg:after >
       <Employees FirstName="Laura" LastName="Fuller" />
       <Employees FirstName="Janet" LastName="Pecock" />
    </updg:after>
  </updg:sync>
</ROOT>

To test the updategram

Save the updategram (MyUpdategram.xml) in the folder that is associated with the virtual name of template type, and then execute the updategram by using the following URL:

http://IISServer/VirtualRoot/TemplateVirtualName/MyUpdategram.xml

This revised updategram uses two separate <after> blocks instead of one block to insert the two employees:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync>
    <updg:after >
       <Employees FirstName="Laura" LastName="Fuller" />
    </updg:after>
    <updg:before>
    </updg:before>
    <updg:after >
       <Employees FirstName="Janet" LastName="Pecock" />
    </updg:after>
  </updg:sync>
</ROOT>
C. Dealing with valid SQL Server characters that are 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 is not valid in XML haracters that are valid SQL Server identifiers but not valid XML identifiers can be encoded using '__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.

Also, the element name must be enclosed within brackets ([ ]). Because the characters [ and ] are not valid in XML, you must encode them as _x005B_ and _x005D_, respectively. (If you use a mapping schema, you can provide element names that do not contain characters that are not valid, such as white spaces. The mapping schema does the necessary mapping; therefore, you do not need to encode for these characters).

This updategram adds a record to the Order Details table in the Northwind database:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync >
    <updg:before>
    </updg:before>
    <updg:after>
      <_x005B_Order_x0020_Details_x005D_ OrderID="1"
            ProductID="11"
            UnitPrice="$1.0"
            Quantity="1"
            Discount="0.0" />
    </updg:after>
  </updg:sync>
</ROOT>

The UnitPrice column in the Order Details table is of the money type. To apply the appropriate type conversion (from a string type to a money type), the dollar sign character ($) must be added as part of the value. If the updategram does not specify a mapping schema, the first character of the string value is evaluated. If the first character is a dollar sign ($), the appropriate conversion is applied. If a mapping schema is provided and the column is appropriately marked as dt:type=fixed.14.4, the dollar sign ($) is not required. If the updategram is specified against a mapping schema, the conversion is handled by the mapping.

To test the updategram

Save the updategram (MyUpdategram.xml) in the folder that is associated with the virtual name of template type, and then execute the updategram by using the following URL:

http://IISServer/VirtualRoot/TemplateVirtualName/MyUpdategram.xml
D. Using the at-identity attribute to retrieve the value that has been inserted in the IDENTITY-type column

This updategram inserts two records: one in the Orders table and another in the Order Details table.

First, the updategram adds a record to the Orders table. In this table, the OrderID column is an IDENTITY-type column. Therefore, when you add this record to the table, the updategram uses the at-identity attribute to capture the assigned OrderID value as "x" (a place holder value). The updategam then specifies this at-identity variable as the value of OrderID attribute in the <_x005B_Order_x0020_Details_x005D_> element.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
 <updg:sync >
  <updg:before>
  </updg:before>
  <updg:after>
   <Orders updg:at-identity="x" CustomerID="ALFKI" OrderDate="07/18/00" />
      <_x005B_Order_x0020_Details_x005D_ OrderID="x"
            ProductID="11"
            UnitPrice="$1.0"
            Quantity="1"
            Discount="0.0" />
    </updg:after>
  </updg:sync>
</ROOT>

If you want to return the identity value that is generated by the updg:at-identity attribute, you can use the updg:returnid attribute. The following is a revised updategram that returns this identity value. (This updategram adds two order records and two order detail records, just to make the example a little more complex.)

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
 <updg:sync>
  <updg:before>
  </updg:before>
  <updg:after updg:returnid="x y" >
   <Orders updg:at-identity="x" CustomerID="ALFKI" 
                                OrderDate="11/3/2000" />
      <_x005B_Order_x0020_Details_x005D_ OrderID="x"
            ProductID="9"
            UnitPrice="$9.0"
            Quantity="1"
            Discount="0.0" />
   <Orders updg:at-identity="y" CustomerID="ALFKI" 
                                OrderDate="11/3/2000" />
      <_x005B_Order_x0020_Details_x005D_ OrderID="y"
            ProductID="10"
            UnitPrice="$10.0"
            Quantity="1"
            Discount="0.0" />
  </updg:after>
 </updg:sync>
</ROOT>

When the updategram is executed, it returns the following result, which includes the identity value that was generated:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> 
  <returnid> 
    <x>11091</x> 
    <y>11092</y> 
  </returnid> 
</ROOT>

To test the updategram

Save the updategram (MyUpdategram.xml) in the folder that is associated with the virtual name of template type, and then execute the updategram by using the following URL:

http://IISServer/VirtualRoot/TemplateVirtualName/MyUpdategram.xml
E. Using the updg:guid attribute to generate a unique value

In this example, the updategram inserts a record in the Cust and CustOrder tables. Also, the updategram generates a unique value for the CustomerID attribute by using the updg:guid attribute.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync >
    <updg:before>
    </updg:before>
    <updg:after updg:returnid="x" >
      <Cust updg:guid="x" >
         <CustID>x</CustID>
         <LastName>Fuller</LastName>
      </Cust>
      <CustOrder>
         <CustID>x</CustID>
         <OrderID>1</OrderID>
      </CustOrder>
    </updg:after>
  </updg:sync>
</ROOT>

The updategram specifies the returnid attribute. As a result, the GUID that is generated is returned:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <returnid>
    <x>7111BD1A-7F0B-4CEE-B411-260DADFEFA2A</x> 
  </returnid>
</ROOT>

To test the updategram

  1. Save the updategram (MyUpdategram.xml) in the folder that is associated with the virtual name of template type.

  2. Create these tables: 
    CREATE TABLE Cust (CustID uniqueidentifier, LastName varchar(20))
CREATE TABLE CustOrder (CustID uniqueidentifier, OrderID int)
  3. Execute the updategram by using this URL: 
    http://IISServer/VirtualRoot/TemplateVirtualName/MyUpdategram.xml
F. Specifying a schema in an updategram

The updategram in this example inserts a record into the table CustOrder(OrderID, EmployeeID, OrderType).

An XSD schema is specified in this updategram (that is, there is no default mapping of updategram elements and attributes). The schema provides the necessary mapping of the elements and attributes to the database tables and columns.

The schema describes a <CustOrder> element that consists of the OrderID and EmployeeID attributes. To make the schema more interesting, a default value is assigned to the EmployeeID attribute. An updategram uses an attribute's default value only for insert operations, and then only if the updategram does not specify that attribute.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="CustOrder" >
   <xsd:complexType>
        <xsd:attribute name="OrderID"     type="xsd:integer" /> 
        <xsd:attribute name="EmployeeID"  type="xsd:integer" />
        <xsd:attribute name="OrderType  " type="xsd:integer" default="1"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

This updategram inserts a record into the CustOrder table. The updategram specifies only the OrderID and EmployeeID attribute values. It does not specify the OrderType attribute value. Therefore, the updategram uses the default value of the EmployeeID attribute that is specified in the preceding schema.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
             xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema='SampleSchema.xml'>
<updg:after>
       <CustOrder OrderID="98000" EmployeeID="1" />
</updg:after>
</updg:sync>
</ROOT>

For more examples of updategrams that specify a mapping schema, see Specifying an Annotated Mapping Schema in an Updategram.

To test the updategram

  1. Create this table in the database that is associated with the virtual root: 
    CREATE TABLE CustOrder(
                 OrderID int, 
                 EmployeeID int, 
                 OrderType int)
  2. Save the schema (SampleSchema.xml) in the folder that is associated with the virtual root of template type.

  3. Save the updategram (SampleUpdategram.xml) in the folder that is associated with the virtual name of template type.

  4. Execute the updategram by using this URL: 
    http://IISServer/VirtualRoot/TemplateVirtualName/SampleUpdategram.xml

This is the equivalent XDR schema:

<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <ElementType name="CustOrder" >
    <AttributeType name="OrderID" />
    <AttributeType name="EmployeeID" />
    <AttributeType name="OrderType" default="1" />
    <attribute type="OrderID"  />
    <attribute type="EmployeeID" />
    <attribute type="OrderType" />
  </ElementType>
</Schema>
G. Using the xsi:nil attribute to insert null values in a column

If you want to insert a null value in the corresponding column in the table, you can specify the xsi:nil attribute on an element in an updategram. In the corresponding XSD schema, the nullable XSD attribute also must be specified.

For example, consider this XSD schema:

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Student" sql:relation="Students">
  <xsd:complexType>
    <xsd:all>
      <xsd:element name="fname" sql:field="first_name" 
                                type="xsd:string" 
                                 nillable="true"/>
    </xsd:all>
    <xsd:attribute name="SID" 
                        sql:field="StudentID"
                        type="xsd:ID"/>    
    <xsd:attribute name="lname"     
                        sql:field="last_name"
                        type="xsd:string"/>
    <xsd:attribute name="minitial"  
                        sql:field="middle_initial" 
                        type="xsd:string"/>
    <xsd:attribute name="years"     
                         sql:field="no_of_years"
                         type="xsd:integer"/>
  </xsd:complexType>
</xsd:element>

</xsd:schema>

The XSD schema specifies nillable="true" for the <fname> element. The following updategram uses this schema:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
      xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<updg:sync mapping-schema='SampleSchema.xml'>
  <updg:before/>
  <updg:after>
    <Student SID="S00004" lname="Elmaci" minitial="" years="2">
      <fname xsi:nil="true">
    </fname>
    </Student>
  </updg:after>
</updg:sync>

</ROOT>

The updategram specifies xsi:nil for the <fname> element in the <after> block. Therefore, when this updategram is executed, a value of NULL is inserted for the first_name column in the table.

To test the updategram

  1. Create the following table in the database that is associated with the virtual root: 
    CREATE TABLE Students (
    StudentID char(6)NOT NULL ,
    first_name varchar(50),
    last_name varchar(50),
    middle_initial char(1),
    no_of_years int NULL)
GO
  2. Save the schema (SampleSchema.xml) in the folder that is associated with the virtual root of template type.

  3. Save the updategram (SampleUpdategram.xml) in the folder that is associated with the virtual name of template type.

  4. Execute the updategram by using this URL: 
    http://IISServer/VirtualRoot/TemplateVirtualName/SampleUpdategram.xml
H. Specifying namespaces in an updategram

In an updategram you can have elements that belong to a namespace declared in the same element in the updategram. In this case, the corresponding schema must also declare the same namespace and the element must belong to that target namespace.

For example, in the following updategram, the <Order> element belongs to a namespace declared in the element.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync mapping-schema='XSD_ElementHavingNameSpace.xml'>  
    <updg:after>
       <x:Order OrderID="10250" EmployeeID="7"  
                OrderDate="2000-12-31" 
                xmlns:x="http://server/xyz/schemas/"/>
    </updg:after>
  </updg:sync>
</ROOT>

In this case, the schema must also declare the namespace as shown in this schema:

This is the schema which illustrate how the corresponding element must declared.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
     xmlns:dt="urn:schemas-microsoft-com:datatypes" 
     xmlns:sql="urn:schemas-microsoft-com:mapping-schema"  
     xmlns:x="http://server/xyz/schemas/" 
     targetNamespace="http://server/xyz/schemas/" >
 
  <xsd:element name="Order" sql:relation="Orders" type="x:Order_type"/>
  <xsd:complexType name="Order_type">
    <xsd:attribute name="OrderID"  type="xsd:ID"/>
    <xsd:attribute name="EmployeeID" type="xsd:string"/>
    <xsd:attribute name="OrderDate" type="xsd:dateTime"/>
  </xsd:complexType>
</xsd:schema>

Updategram and DiffGram Security Issues

© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement | Site Feedback
Page view tracker