Inserting Data Using XML Updategrams (SQLXML 4.0)
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 child elements 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 Using XML Updategrams (SQLXML 4.0).
updg:at-identity Attribute
When an 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
To create working samples using the following examples, you must meet the requirements specified in Requirements for Running SQLXML 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 (SQLXML 4.0).
Most of the examples use the AdventureWorks2008R2 sample database. All the updates are applied to the tables in this database. You can restore the AdventureWorks2008R2 database. For information about restoring the AdventureWorks2008R2 database, see AdventureWorks Sample Databases.
A. Inserting a record by using an updategram
This attribute-centric updategram inserts a record in the HumanResources.Employee table in the AdventureWorks2008R2 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 child elements map to columns in that table.
The AdventureWorks2008R2 schema for the HumanResources.Employee table imposes a 'not null' restriction on all columns except OrganizationNode, and OrganizationLevel. Therefore, the updategram must include values specified for all columns but these two columns. The BusinessEntityID is an IDENTITY-type column. Therefore, no values are specified for it.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
</updg:before>
<updg:after>
<HumanResources.Employee
NationalIDNumber="111111111"
BusinessEntityID="128"
LoginID="adventure-works\paulw0"
DepartmentID="7"
ShiftID="3"
JobTitle="Production Technician - WC40"
EmergencyContactID="1445"
AddressID="157"
BirthDate="1974-12-15 00:00:00.000"
MaritalStatus="S"
Gender="M"
HireDate="2003-01-05 00:00:00.000"
SalariedFlag="0"
BaseRate="15.00"
PayFrequency="1"
VacationHours="80"
SickLeaveHours="80"
CurrentFlag="1"
rowguid="00001111-2222-3333-4444-555566667777"
ModifiedDate="2008-7-31 00:00:00.000"/>
</updg:after>
</updg:sync>
</ROOT>
To test a sample XPath query against the schema
Copy the updategram above and paste it into a text file. Save the file as MyUpdategram.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
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>
<HumanResources.Employee>
<NationalIDNumber>111111111</NationalIDNumber>
<BusinessEntityID>128</BusinessEntityID>
<LoginID>adventure-works\paulw0</LoginID>
<DepartmentID>7</DepartmentID>
<ShiftID>3</ShiftID>
<JobTitle>Production Technician - WC40</JobTitle>
<EmergencyContactID>1445</EmergencyContactID>
<AddressID>157</AddressID>
<BirthDate>1974-12-15 00:00:00.000</BirthDate>
<MaritalStatus>S</MaritalStatus>
<Gender>M</Gender>
<HireDate>2003-01-05 00:00:00.000</HireDate>
<SalariedFlag>0</SalariedFlag>
<BaseRate>15.00</BaseRate>
<PayFrequency>1</PayFrequency>
<VacationHours>80</VacationHours>
<SickLeaveHours>80</SickLeaveHours>
<CurrentFlag>1</CurrentFlag>
<rowguid>00001111-2222-3333-4444-555566667777</rowguid>
<ModifiedDate>2008-7-31 00:00:00.000</ModifiedDate>
</HumanResources.Employee>
</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>
<HumanResources.Employee
NationalIDNumber="111111111"
BusinessEntityID="128"
DepartmentID="7"
ShiftID="3"
EmergencyContactID="1445"
AddressID="157"
SalariedFlag="0"
BaseRate="15.00"
PayFrequency="1"
VacationHours="80"
SickLeaveHours="80"
CurrentFlag="1">
<LoginID>adventure-works\paulw0</LoginID>
<JobTitle>Production Technician - WC40</JobTitle>
<BirthDate>1974-12-15 00:00:00.000</BirthDate>
<HireDate>2003-01-05 00:00:00.000</HireDate>
<MaritalStatus>S</MaritalStatus>
<Gender>M</Gender>
<rowguid>00001111-2222-3333-4444-555566667777</rowguid>
<ModifiedDate>2008-7-31 00:00:00.000</ModifiedDate>
</HumanResources.Employee>
</updg:after>
</updg:sync>
</ROOT>
B. Inserting multiple records by using an updategram
This updategram adds two new shift records to the HumanResources.Shift table. The updategram does not specify the optional <before> block.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:after >
<HumanResources.Shift Name="Day-Evening"
StartTime="1900-01-01 11:00:00.000"
EndTime="1900-01-01 19:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
<HumanResources.Shift Name="Evening-Night"
StartTime="1900-01-01 19:00:00.000"
EndTime="1900-01-01 03:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
</updg:after>
</updg:sync>
</ROOT>
To test a sample XPath query against the schema
Copy the updategram above and paste it into a text file. Save the file as Updategram-AddShifts.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Another version of this example is an updategram that uses two separate <after> blocks instead of one block to insert the two employees. This is valid and can be encoded as follows:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:after >
<HumanResources.Shift Name="Day-Evening"
StartTime="1900-01-01 11:00:00.000"
EndTime="1900-01-01 19:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
</updg:after>
<updg:before>
</updg:before>
<updg:after >
<HumanResources.Shift Name="Evening-Night"
StartTime="1900-01-01 19:00:00.000"
EndTime="1900-01-01 03:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
</updg:after>
</updg:sync>
</ROOT>
C. Working 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 characters 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.
Note
This example uses the Northwind database, which was first provided with SQL Server 2000 as a sample database. The Northwind database is not provided with later versions of SQL Server. However, you can install the Northwind database by using an SQL script available for download. For more information, see Downloading Northwind and pubs Sample Databases.
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 the updategram is specified against a mapping schema where the column is appropriately marked as either dt:type="fixed.14.4" or sql:datatype="money", the dollar sign ($) is not required and the conversion is handled by the mapping. This is the recommended way to ensure that the appropriate type conversion occurs.
To test a sample XPath query against the schema
Copy the updategram above and paste it into a text file. Save the file as UpdategramSpacesInTableName.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
D. Using the at-identity attribute to retrieve the value that has been inserted in the IDENTITY-type column
The following updategram inserts two records: one in the Sales.SalesOrderHeader table and another in the Sales.SalesOrderDetail table.
First, the updategram adds a record to the Sales.SalesOrderHeader table. In this table, the SalesOrderID 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 SalesOrderID value as "x" (a placeholder value). The updategam then specifies this at-identity variable as the value of SalesOrderID attribute in the <Sales.SalesOrderDetail> element.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
</updg:before>
<updg:after>
<Sales.SalesOrderHeader updg:at-identity="x"
RevisionNumber="1"
OrderDate="2001-07-01 00:00:00.000"
DueDate="2001-07-13 00:00:00.000"
OnlineOrderFlag="0"
CustomerID="676"
BillToAddressID="985"
ShipToAddressID="985"
ShipMethodID="5"
SubTotal="24643.9362"
TaxAmt="1971.5149"
Freight="616.0984"
rowguid="00001111-2222-3333-4444-556677889900"
ModifiedDate="2005-07-08 00:00:00.000" />
<Sales.SalesOrderDetail SalesOrderID="x"
LineNumber="1"
OrderQty="1"
ProductID="776"
SpecialOfferID="1"
UnitPrice="2429.9928"
UnitPriceDiscount="0.00"
rowguid="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
ModifiedDate="2005-07-01 00:00:00.000" />
</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" >
<HumanResources.Shift updg:at-identity="x" Name="Day-Evening"
StartTime="1900-01-01 11:00:00.000"
EndTime="1900-01-01 19:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
<HumanResources.Shift updg:at-identity="y" Name="Evening-Night"
StartTime="1900-01-01 19:00:00.000"
EndTime="1900-01-01 03:00:00.000"
ModifiedDate="2004-01-01 00:00:00.000" />
</updg:after>
</updg:sync>
</ROOT>
When the updategram is executed, it returns results similar to the following, which includes the identity value (the generated value of the ShiftID column used for table identity) that was generated:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<returnid>
<x>4</x>
<y>5</y>
</returnid>
</ROOT>
To test a sample XPath query against the schema
Copy the updategram above and paste it into a text file. Save the file as Updategram-returnId.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
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
Copy the updategram above and paste it into a text file. Save the file as Updategram-GenerateGuid.xml.
Create these tables:
USE tempdb CREATE TABLE Cust (CustID uniqueidentifier, LastName varchar(20)) CREATE TABLE CustOrder (CustID uniqueidentifier, OrderID int)Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
F. Specifying a schema in an updategram
The updategram in this example inserts a record into the following table:
CustOrder(OrderID, BusinessEntityID, 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 following schema (CustOrderSchema.xml) describes a <CustOrder> element that consists of the OrderID and BusinessEntityID attributes. To make the schema more interesting, a default value is assigned to the BusinessEntityID 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="BusinessEntityID" 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 BusinessEntityID attribute values. It does not specify the OrderType attribute value. Therefore, the updategram uses the default value of the BusinessEntityID 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='CustOrderSchema.xml'>
<updg:after>
<CustOrder OrderID="98000" BusinessEntityID="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 (SQLXML 4.0).
To test the updategram
Create this table in the tempdb database:
USE tempdb CREATE TABLE CustOrder( OrderID int, BusinessEntityID int, OrderType int)Copy the schema above and paste it into a text file. Save the file as CustOrderSchema.xml.
Copy the updategram above and paste it into a text file. Save the file as CustOrderUpdategram.xml in the same folder used in the previous step.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the updategram.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
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="BusinessEntityID" />
<AttributeType name="OrderType" default="1" />
<attribute type="OrderID" />
<attribute type="BusinessEntityID" />
<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 XSD nillable 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='StudentSchema.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
Create the following table in the tempdb database:
USE tempdb 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) GOCopy the schema above and paste it into a text file. Save the file as StudentSchema.xml.
Copy the updategram above and paste it into a text file. Save the file as StudentUpdategram.xml in the same folder used in previous step to save StudentSchema.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the updategram.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
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 (UpdateGram-ElementHavingNamespace.xml), 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 xmlns:x="https://server/xyz/schemas/"
updg:at-identity="SalesOrderID"
RevisionNumber="1"
OrderDate="2005-07-01 00:00:00.000"
DueDate="2005-07-13 00:00:00.000"
OnlineOrderFlag="0"
CustomerID="676"
BillToAddressID="985"
ShipToAddressID="985"
ShipMethodID="5"
SubTotal="24643.9362"
TaxAmt="1971.5149"
Freight="616.0984"
rowguid="00009999-8888-7777-6666-554433221100"
ModifiedDate="2005-07-08 00:00:00.000" />
</updg:after>
</updg:sync>
</ROOT>
In this case, the schema must also declare the namespace as shown in this schema:
The following schema (XSD-ElementHavingNamespace.xml) shows how the corresponding element and attributes must be 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="https://server/xyz/schemas/"
targetNamespace="https://server/xyz/schemas/" >
<xsd:element name="Order" sql:relation="Sales.SalesOrderHeader" type="x:Order_type"/>
<xsd:complexType name="Order_type">
<xsd:attribute name="SalesOrderID" type="xsd:ID"/>
<xsd:attribute name="RevisionNumber" type="xsd:unsignedByte"/>
<xsd:attribute name="OrderDate" type="xsd:dateTime"/>
<xsd:attribute name="DueDate" type="xsd:dateTime"/>
<xsd:attribute name="ShipDate" type="xsd:dateTime"/>
<xsd:attribute name="Status" type="xsd:unsignedByte"/>
<xsd:attribute name="OnlineOrderFlag" type="xsd:boolean"/>
<xsd:attribute name="SalesOrderNumber" type="xsd:string"/>
<xsd:attribute name="PurchaseOrderNumber" type="xsd:string"/>
<xsd:attribute name="AccountNumber" type="xsd:string"/>
<xsd:attribute name="CustomerID" type="xsd:int"/>
<xsd:attribute name="SalesPersonID" type="xsd:int"/>
<xsd:attribute name="TerritoryID" type="xsd:int"/>
<xsd:attribute name="BillToAddressID" type="xsd:int"/>
<xsd:attribute name="ShipToAddressID" type="xsd:int"/>
<xsd:attribute name="ShipMethodID" type="xsd:int"/>
<xsd:attribute name="CreditCardID" type="xsd:int"/>
<xsd:attribute name="CreditCardApprovalCode" type="xsd:string"/>
<xsd:attribute name="CurrencyRateID" type="xsd:int"/>
<xsd:attribute name="SubTotal" type="xsd:decimal"/>
<xsd:attribute name="TaxAmt" type="xsd:decimal"/>
<xsd:attribute name="Freight" type="xsd:decimal"/>
<xsd:attribute name="TotalDue" type="xsd:decimal"/>
<xsd:attribute name="Comment" type="xsd:string"/>
<xsd:attribute name="rowguid" type="xsd:string"/>
<xsd:attribute name="ModifiedDate" type="xsd:dateTime"/>
</xsd:complexType>
</xsd:schema>
To test the updategram
Copy the schema above and paste it into a text file. Save the file as XSD-ElementHavingNamespace.xml.
Copy the updategram above and paste it into a text file. Save the file as Updategram-ElementHavingNamespace.xml in the same folder used to save XSD-ElementHavingnamespace.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the updategram.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
I. Inserting data into an XML data type column
The xml data type was introduced in SQL Server 2005. You can use updategrams to insert and update data stored in xml data type columns with the following provisions:
The xml column cannot be used for identifying an existing row. Therefore, it cannot be included in the updg:before section of an updategram.
Namespaces that are in scope of the XML fragment inserted into the xml column will be preserved and their namespace declarations are added to the top element of the inserted fragment.
For example, in the following updategram (SampleUpdateGram.xml), the <Desc> element updates the ProductDescription column in the Production>productModel table in the AdventureWorks2008R2 sample database. The result of this updategram is that the XML contents of the ProductDescription column are update with the XML contents of the <Desc> element.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="SampleSchema.xml" >
<updg:before>
<ProductModel ProductModelID="19">
<Name>Mountain-100</Name>
</ProductModel>
</updg:before>
<updg:after>
<ProductModel>
<Name>Mountain-100</Name>
<Desc><?xml-stylesheet href="ProductDescription.xsl" type="text/xsl"?>
<p1:ProductDescription xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"
xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"
xmlns:wf="https://www.adventure-works.com/schemas/OtherFeatures"
xmlns:html="http://www.w3.org/1999/xhtml"
>
<p1:Summary>
<html:p>Insert Example</html:p>
</p1:Summary>
<p1:Manufacturer>
<p1:Name>AdventureWorks2008R2</p1:Name>
<p1:Copyright>2006</p1:Copyright>
<p1:ProductURL>HTTP://www.Adventure-works.com</p1:ProductURL>
</p1:Manufacturer>
<p1:Features>These are the product highlights.
<wm:Warranty>
<wm:WarrantyPeriod>3 years</wm:WarrantyPeriod>
<wm:Description>parts and labor</wm:Description>
</wm:Warranty>
<wm:Maintenance>
<wm:NoOfYears>10 years</wm:NoOfYears>
<wm:Description>maintenance contract available through your dealer or any Adventure Works retail store.</wm:Description>
</wm:Maintenance>
<wf:wheel>High performance wheels.</wf:wheel>
<wf:saddle>
<html:i>Anatomic design</html:i> and made from durable leather for a full-day of riding in comfort.</wf:saddle>
<wf:pedal>
<html:b>Top-of-the-line</html:b> clipless pedals with adjustable tension.</wf:pedal>
<wf:BikeFrame>Each frame is hand-crafted in our Bothell facility to the optimum diameter and wall-thickness required of a premium mountain frame. The heat-treated welded aluminum frame has a larger diameter tube that absorbs the bumps.</wf:BikeFrame>
<wf:crankset> Triple crankset; alumunim crank arm; flawless shifting. </wf:crankset>
</p1:Features>
<p1:Picture>
<p1:Angle>front</p1:Angle>
<p1:Size>small</p1:Size>
<p1:ProductPhotoID>118</p1:ProductPhotoID>
</p1:Picture>
<p1:Specifications> These are the product specifications.
<Material>Almuminum Alloy</Material>
<Color>Available in most colors</Color>
<ProductLine>Mountain bike</ProductLine>
<Style>Unisex</Style>
<RiderExperience>Advanced to Professional riders</RiderExperience>
</p1:Specifications>
</p1:ProductDescription>
</Desc>
</ProductModel>
</updg:after>
</updg:sync>
</ROOT>
The updategram refers to the following annotated XSD schema (SampleSchema.xml).
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
<xsd:element name="ProductModel" sql:relation="Production.ProductModel" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Name" type="xsd:string"></xsd:element>
<xsd:element name="Desc" sql:field="CatalogDescription" sql:datatype="xml">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductDescription">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Summary" type="xsd:anyType">
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ProductModelID" sql:field="ProductModelID"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
To test the updategram
Copy the schema above and paste it into a text file. Save the file as XSD-SampleSchema.xml.
Note
Because updategrams support default mapping, there is no way to identify the beginning and ending of the xml data type. This effectively means that a mapping schema is required when inserting or updating tables with xml data type columns. When a schema is not provided, SQLXML returns an error indicating that one of the columns is missing from the table.
Copy the updategram above and paste it into a text file. Save the file as SampleUpdategram.xml in the same folder used to save SampleSchema.xml.
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the updategram.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.