Contents
Overview 1
Modifying Data with Updategrams 2
Lab 7.1: Using Updategrams 17
Inserting XML Data with the Bulk Load
Facility 22
Lab 7.2: Bulk Loading XML Data 31
Best Practices 34
Review 36

Module 7: XML for SQL
Tools

Information in this document is subject to change without notice. The names of companies,
products, people, characters, and/or data mentioned herein are fictitious and are in no way intended
to represent any real individual, company, product, or event, unless otherwise noted. Complying
with all applicable copyright laws is the responsibility of the user. No part of this document may
be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose, without the express written permission of Microsoft Corporation. If, however, your only
means of access is electronic, permission to print one copy is hereby granted.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering subject matter in this document. Except as expressly provided in any
written license agreement from Microsoft, the furnishing of this document does not give you any
license to these patents, trademarks, copyrights, or other intellectual property.

®
PowerPoint
®
file 2091a_07.ppt.

Preparation Tasks
To prepare for this module, you should:

Read all of the materials for this module.

Complete the labs.

Presentation:
90 Minutes

Lab:
45 Minutes
iv Module 7: XML for SQL Tools

Module Strategy
Use the following strategy to present this module:

Modifying Data with Updategrams
Point out that an Updategram is conceptually a specialized form of template.
Emphasize the use of Updategrams to enable full data modification over
HTTP in a SQLISAPI virtual directory–based application. This means that
you can build a full data-enabled Web solution without any Active Server
Pages (ASP) code.

Inserting XML Data with the Bulk Load Facility

®
Web site at
Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
In this module, you will learn
about the additional
functionality available in the
XML for SQL Web Update.
For Your Information
You can download the XML
for SQL Web Update from
the MSDN Web site at

2 Module 7: XML for SQL Tools





Modifying Data with Updategrams

Using an Updategram to Modify Data

Structure of an XML Updategram

Mapping Updategram Values to SQL Data



Using an Updategram to Modify Data
Shipping Co.
Supplier
INSERT
UPDATE
DELETE
XML delivery data

*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
Similar to template files, Updategrams are XML documents that you can
submit as commands to SQL Server over a Microsoft ActiveX
®
Data Objects
(ADO) or Hypertext Transfer Protocol (HTTP) connection. The main difference
is that templates are used to retrieve data, while Updategrams are used to
modify data.
Use an Updategram for data modifications that must be:

Performed from a Web site that is based on a SQL Internet Server
Application Programming Interface (SQLISAPI). In this case, templates or
mapping schemas could be used to retrieve data, and Updategrams could be
used to insert, update, or delete data.

Submitted by an organization to a trading partner over the Internet.


</updg:sync>
</root>
<root xmlns:updg="urn:schemas-microsoft-com:xml-
updategram">
<updg:sync [mapping-schema=mapping schema]>
<updg:before>
XML Fragment
</updg:before>
<updg:after>
XML Fragment
</updg:after>
</updg:sync>
</root>

*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
An Updategram specifies what the XML data looks like now and what it will
look like after the Updategram is executed.
Specifying the xml-Updategram Namespace
Updategrams must reference the Microsoft xml-Updategram namespace
(schemas-microsoft-com:xml-Updategram). This namespace defines the
elements and attributes that are used in an Updategram.
You can reference the xml-Updategram namespace by adding an xmlns
attribute declaration in the root element of the Updategram, as shown in the
following example.
<root xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">


Specifying a Mapping Schema

Specify a mapping-schema attribute for the <sync>
element

*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
You can resolve XML elements and attributes in a before or after element of an
Updategram to the corresponding SQL Server data in two ways.
Using a Default Mapping
If no schema is specified, SQL Server will apply a default (or implicit) mapping
between the XML data in the Updategram and the data in the database.
Consider the following default mapping rules:

Elements that contain attributes or child elements map to table names.

XML attributes and child elements map to database columns that have the
same name as the attribute or element.

The following Updategram modifies the ProductName column of the Products
table:
<productupdate
xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">
<updg:sync >
<updg:before>
<Products ProductID="1">
<ProductName>Chai</ProductName>

<productupdate
xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">
<updg:sync mapping-schema="c:\productschema.xml">
<updg:before>
<Product ProductCode="1">
<ProductName>Chai</ProductName>
</Products>
</updg:before>
<updg:after>
<Product ProductCode="1">
<ProductName>Tea</ProductName>
</Products>
</updg:after>
</updg:sync>
</productupdate>

Syntax
Example
Module 7: XML for SQL Tools 7

Inserting Data with an Updategram

Inserting a Row

Specify an after image of the data

Inserting IDENTITY Values

Use the at-identity attribute for IDENTITY columns



If the table contains columns that are not specified in the Updategram, the
default values for those columns are inserted. If no default values are defined,
SQL Server attempts to insert NULL. If NULL values are not permitted in the
column, an error occurs and the entire insertion is cancelled.
You can insert multiple rows in a single <sync> element by declaring an XML
element for each row to be inserted.
Topic Objective
To describe how to perform
INSERT operations with an
Updategram.
Lead-in
You can use an
Updategram to insert rows
into a database.
Example
8 Module 7: XML for SQL Tools

Inserting IDENTITY Values
When a table contains an IDENTITY column, SQL Server automatically
generates the value of the column for all new records. You can insert an
IDENTITY value by using the at-identity attribute, and retrieve it by using the
returnid attribute. This is shown in the following example.
<AddOrder
xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">
<updg:sync>
<updg:before>
</updg:before>
<updg:after updg:returnid="x">
<Orders updg:at-identity="x"

</updg:before>
<updg:after>
<Customers updg:guid="x">
<CustomerID>x</CustomerID>
<CustomerName>Graeme</CustomerName>
</Customers>
</updg:after>
</updg:sync>
</NewCustomer>

Example
For Your Information
In the Updategram example,
the space in Order Details is
represented by the encoded
value _x0020_. This is
necessary because the
space character is illegal in
XML. The UnitPrice value is
converted to the SQL Server
data type “money” by
prefixing it with a “$”
character. This is required
for all money columns
unless you use a schema
declaring the column as
XML data type “fixed.14.4”.
Example
For Your Information
This example will not work

</DeleteProduct>

*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
You can use Updategrams to delete data.
Deleting a Single Record
Deleting a single record with an Updategram involves two easy steps:
1. First, specify a before image of the data. The contents of the before image
act like a SQL WHERE clause. Each element in the before element must
uniquely identify a single record to be deleted.
2. Specify an empty after image.

Topic Objective
To describe how to perform
DELETE operations with an
Updategram.
Lead-in
You can use an
Updategram to delete data
from the database.
10 Module 7: XML for SQL Tools

The following Updategram deletes any record in the Products table matching a
Product ID of 101:
<DeleteProduct
xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">
<updg:sync>

<updg:sync >
<updg:before>
<Products ProductID="101"/>
</updg:before>
<updg:after>
<Products UnitPrice="$752.99"/>
</updg:after>
</updg:sync>
</UpdateProduct>
<UpdateProduct
xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<Products ProductID="101"/>
</updg:before>
<updg:after>
<Products UnitPrice="$752.99"/>
</updg:after>
</updg:sync>
</UpdateProduct>

*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
You can use Updategrams to update data in the database.
Specifying “before” and “after” Images of the Data
When you update records, you use the elements identified in the <updg:before>
element to specify which rows to update. You use the elements in the

The following Updategram updates the CategoryID field for products 101
and 102:
<UpdateProducts
xmlns:updg="urn:schemas-microsoft-com:xml-Updategram">
<updg:sync >
<updg:before>
<Products updg:id="x" ProductID="101"/>
<Products updg:id="y" ProductID="102"/>
</updg:before>
<updg:after>
<Products updg:id="x" CategoryID="2"/>
<Products updg:id="y" CategoryID="2"/>
</updg:after>
</updg:sync>
</UpdateProducts>

Example