Chapter 2
Storing XML Data in SQL Tables
IN THIS CHAPTER
Adding XML data to an SQL pseudotable
Designing tables to store XML data
Bringing XML documents current
Getting to know Oracle’s tools for updating tables with XML
Discovering Microsoft’s tools for updating tables with XML
The latest version of the ISO/IEC SQL specification (SQL:2016) details how to store XML data in an SQL-compliant database and operate on it with SQL. In this chapter, I cover SQL’s basic data manipulation operations as applied to XML data. Because the primary focus of this book is SQL, I assume that you’re already up to speed on XML.
Inserting XML Data into an SQL Pseudotable
Until recently, with regard to the relationship between SQL and XML, the emphasis has been on converting SQL table data to XML to make it accessible on the Internet. A more recent addition to the SQL standard addresses the complementary problem of converting XML data to SQL tables so that it can be easily queried using standard SQL statements. The XMLTABLEXMLTABLE is
XMLTABLE ( [namespace-declaration,]
XQuery-expression
[PASSING argument-list]
COLUMNS XMLtbl-column-definitions
where argument-list is
value-expression AS identifier
and XMLtbl-column-definitions is a comma-separated list of column definitions, which may contain
column-name FOR ORDINALITY
or
column-name data-type
[BY REF | BY VALUE]
[default-clause]
[PATH XQuery-expression]
Here’s an example of how you might use XMLTABLE to extract data from an XML document into an SQL pseudotable. A pseudotable isn’t persistent — meaning that it isn’t permanently stored— but in every other respect behaves like a normal SQL table, as in this example:
SELECT clientphone.*
FROM
clients_xml ,
XMLTABLE(
'for $m in
$col/client
return
$m'
PASSING clients_xml.client AS "col"
COLUMNS
"ClientName" CHAR (30) PATH 'clientname' ,
"Phone" CHAR (13) PATH 'phone'
) AS clientphone
This query retrieves the contents of the clientname and phone fields from the XML document named clients_xml, and places the result set into the ClientName and Phone columns of the SQL pseudotable named clientphone.
When run, the preceding code gives the following result:
ClientName Phone
------------------------------ -------------
Abe Abelson (714)555-1111
Bill Bailey (714)555-2222
Chuck Wood (714)555-3333
(3 rows in clientphone)
If you want to make it persistent, you can create a table with a CREATE TABLE statement as follows:
CREATE TABLE clientphone AS
clients_xml ,
XMLTABLE(
'for $m in
$col/client
return
$m'
PASSING clients_xml.client AS "col"
COLUMNS
"ClientName" CHARACTER (30) PATH 'clientName' ,
"Phone" CHARACTER (13) PATH 'phone'
)
Creating a Table to Hold XML Data
Although you can create a table to hold XML data by using the CREATE TABLE statement wrapped around an XMLTABLE function, as shown in the preceding section, you can also create a table the old-fashioned way, specifying one or more columns as having the XML data type and inserting XML data into the table later. The process is just as simple as this:
CREATE TABLE CLIENT (
ClientName CHAR (30) NOT NULL,
Address1 CHAR (30),
Address2 CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
Phone CHAR (13),
Fax CHAR (13),
ContactPerson CHAR (30),
Comments XML(SEQUENCE) ) ;
Tables can hold a mix of data of the XML data type and classic SQL data types, as shown in this example, or you could create a table in which all columns contain XML data.
Updating XML Documents
Currently, there’s no standard way to update XML documents stored in persistent storage, such as an SQL database. Also, there’s no standard way to modify transient XML documents, such as stock tickers. Furthermore, there’s no standard way to add new data to an existing XML document. Methods for performing these operations haven’t been added to the XQuery 1.0 standard because update operations carry some messy baggage, causing side effects that complicate operations.
Regardless of whether a standard method exists, you need to be able to modify XML documents. Following are some of the required tasks that you should be able to perform:
- Insert new nodes into an instance of a data model at specified positions.
- Change the value of a node in an instance of a data model.
- Replace nodes in an instance of a data model.
- Modify the properties of nodes in an instance of a data model.
- Delete a node in an instance of a data model.
Because you clearly need to be able to update XML documents that reside in SQL databases, and because no universally recognized standard way of doing so exists, vendors of database management systems (DBMS) have developed proprietary solutions to the problem. In the next few sections, I briefly describe the Oracle and Microsoft solutions.
Discovering Oracle’s Tools for Updating XML Data in a Table
Oracle provides three distinct methods of updating XML data in an Oracle database:
- Document Object Model (DOM): DOM was developed by the World Wide Web Consortium (
www.w3.org). It provides methods for traversing the DOM representation of an XML document, retrieving values from individual nodes, inserting nodes, deleting nodes, and modifying the values of nodes. - Java application programming interface (API): Another method uses a Java API that defines a class to represent the XML type, along with methods such as
insertXML(),updateXML(), anddeleteXML(). Update functions: The third method is more closely related to SQL/XML. In SQL/XML, applications use ordinary SQL statements to access XML data stored as values of the XML type in tables. The SQL function
XMLQUERY(discussed in Book 6, Chapter 1) evaluates an XQuery expression and returns the result of that evaluation to an SQL application.Oracle extends SQL/XML with several update functions:
APPENDCHILDXML,INSERTCHILDXML, andINSERTXMLBEFOREfor inserting new dataDELETEXMLfor deleting dataUPDATEXMLfor updating existing data
In the next few sections, I look a bit more closely at the update functions.
APPENDCHILDXML
Unlike the row-and-column structure of an SQL database table, XML documents have a treelike structure. The tree has nodes and branches, with parent nodes branching out to child nodes. The ultimate parent node, called the root node, resides at the base of the tree. The APPENDCHILDXML function adds a child node to an existing node. The node it adds is the very last sibling of the existing node’s current children.
Here’s an example, using the CLIENT table from “Creating a Table to Hold XML Data,” earlier in this chapter:
UPDATE CLIENT SET Comments =
APPENDCHILDXML(Comments, 'Comments/Comment',
XMLTYPE('<IssueClosed>Yes</IssueClosed>'))
WHERE EXTRACTVALUE(Comments,
'/Comments/Comment/ResponseRequested') = 'No';
The second argument of APPENDCHILDXML, 'Comments/Comment', is the XPath expression, which specifies a location within the document. The preceding code makes the following change in the XML document shown in Book 6, Chapter 1: (Xpath is a query language for selecting nodes from an XML document.)
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
<IssueClosed>Yes</IssueClosed>
</Comment>
</Comments>
The IssueClosed node has been added as the last child of the Comment node, where ResponseRequested has a value of No.
INSERTCHILDXML
Whereas APPENDCHILDXML adds a new node to the XML document tree, INSERTCHILDXML inserts a new value into the document at the node specified by the XPath expression. Following is an example:
UPDATE CLIENT SET Comments =
INSERTCHILDXML(Comments, 'Comments/Comment', 'MessageText',
XMLTYPE('<MessageText>I am only interested in gentoo penguins. </MessageText>'))
WHERE EXTRACTVALUE(Comments,
'/Comments/Comment/CommentNo') = 1;
This code adds another instance of MessageText to comment 1, with the following result:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<MessageText>I am only interested in gentoo penguins.</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
<IssueClosed>Yes</IssueClosed>
</Comment>
</Comments>
INSERTXMLBEFORE
The INSERTXMLBEFORE function inserts a new value before the node specified by the XPath expression. The following example shows the difference between INSERTXMLBEFORE and INSERTCHILDXML:
UPDATE CLIENT SET Comments =
INSERTXMLBEFORE(Comments,'Comments/Comment/MessageText[1]',
XMLTYPE('<MessageText>I am only interested in gentoo penguins. </MessageText>'))
WHERE EXTRACTVALUE(Comments,
'/Comments/Comment/CommentNo') = 1;
This code adds another instance of MessageText to comment 1, before the existing instance. The result follows:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>I am only interested in gentoo penguins.</MessageText>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
<IssueClosed>Yes</IssueClosed>
</Comment>
</Comments>
The new addition to Comment 1 has been placed ahead of the original message.
DELETEXML
The DELETEXML function deletes the node matched by the XPath expression in the target XML document. In the example in this section, I remove the IssueClosed node from the Comments document. Here’s the document before the deletion:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
<IssueClosed>Yes</IssueClosed>
</Comment>
</Comments>
Here’s the deletion operation:
UPDATE CLIENT SET Comments =
DELETEXML(Comments, 'Comments/Comment/IssueClosed')
WHERE EXTRACTVALUE(Comments,
'/Comments/Comment/ResponseRequested') = 'No';
The result is
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
</Comment>
</Comments>
UPDATEXML
The UPDATEXML function updates an existing value in an XML document. To see this function in operation, go ahead and change the ResponseRequested element of the Comments document. First, here’s the document before the update:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
</Comment>
</Comments>
Here’s the update operation itself:
UPDATE CLIENT SET Comments =
UPDATEXML(Comments, 'Comments/Comment/ResponseRequested/text()', Maybe)
WHERE EXTRACTVALUE(Comments,
'/Comments/Comment/ResponseRequested') = 'Yes';
This operation produces the following result:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Maybe</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
</Comment>
</Comments>
Oracle’s extension functions UPDATEXML, INSERTCHILDXML, INSERTXMLBEFORE, DELETEXML, and UPDATEXML are transformation functions rather than true update functions. They don’t update an XML value “in place,” but return an updated copy of the value they’ve changed. When the functions are used with an SQL UPDATE statement, as shown here, this difference becomes moot.
Introducing Microsoft’s Tools for Updating XML Data in a Table
Like Oracle, Microsoft provides more than one way to update XML data in its SQL Server 2008 R2 DBMS:
- Using the
modify()method as part of theSETclause of an SQLUPDATEstatement: A parameter determines whether the operation is anINSERT,UPDATE, orDELETEoperation. - Using a set of .NET classes: Some of these classes provide methods for setting the values of nodes, inserting nodes into specified locations, deleting nodes, and replacing nodes.
- Using the
OPENXMLfunction: This function is part of SQL Server’s Transact-SQL implementation of the SQL language. This approach inserts data into a table that pulls its data from an XML document that’s part of SQL Server’s Transact-SQL implementation of the SQL language. Using updategrams: An updategram is a template that contains
<sync>,<before>, and<after>blocks. With an updategram, you can insert, delete, or update XML data in a database table..NET classes is a major topic in its own right, which I will not elaborate on here.
Inserting data into a table using OPENXML
OPENXML is a rowset provider that provides a rowset view over an XML document. To illustrate the use of OPENXML, the following example — using Microsoft’s Transact-SQL — creates an internal example of an XML image, using sp_xml:preparedocument. Next, a SELECT statement uses an OPENXML rowset provider to operate on the internal representation of the XML document.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<MEMBERS MemberID="9"
FirstName="Sam"
LastName="Shovel"
OfficeHeld="Investigator"/>
</ROOT>
--Create an internal representation of the XML document.
EXEC sp_xml:preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT MemberID, FirstName, LastName, OfficeHeld
FROM OPENXML (@idoc, '/ROOT/MEMBERS',1)
WITH (MemberID CHAR(15),
FirstName CHAR(20),
LastName CHAR(20),
OfficeHeld CHAR(20) )
The query produces the following result:
MemberID FirstName LastName OfficeHeld
------------ ------------ ----------- -------------
9 Sam Shovel Investigator
The desired information was pulled from an XML document by an SQL SELECT statement, rather than from an SQL table.
Using updategrams to map data into database tables
An updategram works against the XML views provided by an annotated XSD or XDR schema. (XSD is an XML Schema Definition language used to define an XML schema, whereas XDR — XML-Data Reduced — is an older XML schema definition language.) One example of such a schema is the mapping schema, which has the information needed 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.
Using an updategram namespace and keywords
All three of an updategram’s keywords — <sync>, <before>, and <after> — exist in the namespace urn:schemas-microsoft-com:xml-updategram. You can use any namespace prefix that you want. In the examples in this section, I use updg as a namespace prefix to denote the updategram namespace.
Here’s an example of the template:
<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 code references a mapping-schema named AnnotatedSchemaFile.xml. I discuss mapping schemas in the following section.
The three keywords are defined as follows:
<before>: The state of a record instance before the update.<after>: The state the record instance is to have after the update.<sync>: A block that contains the<before>and<after>blocks. A<sync>block may contain more than one set of<before>and<after>blocks, which are always specified in pairs. A sync block is an atomic item; either all of it is processed or none of it is. In that sense, it’s similar to a transaction in SQL. If you specify multiple<sync>blocks in an updategram and one of them fails, the other<sync>blocks proceed normally, unaffected by the failure. Thus, an updategram itself is not atomic.
You can insert, update, or delete data with an updategram. Which operation is performed depends on the contents of the <before> and <after> blocks:
- If the
<before>block is empty but the<after>block contains a record instance, an insert operation is being performed. - If the
<before>block contains a record instance but the<after>block is empty, a delete operation is being performed. - If both the
<before>block and the<after>block contain a record instance, the record instance in the<before>block is being updated to the record instance in the<after>block.
Specifying a mapping schema
Because the tree structure of an XML document is fundamentally different from the row-and-column structure of an SQL table, there must be a translation from one structure to another for XML data to be placed in an SQL table, and vice versa. This translation is called a mapping schema. In the simplest case, each element in a <before> block or <after> block maps to a table, and each element’s child element or attribute maps to a column in its corresponding table. This situation is called implicit or default mapping. If such simple correspondence between the XML document and the SQL table doesn’t exist, you must explicitly specify a mapping schema in which the elements and attributes of the updategram match the elements and attributes of the mapping schema.
Implicit mapping
In many cases, an updategram can perform an update without an explicit mapping schema, relying on the default mapping schema instead.
INSERTING AN ELEMENT OF AN XML DOCUMENT INTO A RECORD IN AN SQL DATABASE
Look at this example of an insert operation that uses implicit mapping:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
</updg:before>
<updg:after>
<OLS.MEMBERS MemberID="9"
FirstName="Sam"
LastName="Shovel"
OfficeHeld="Investigator"
Email="[email protected]"
Phone="(503)555-8004"
Street="154 Polk St."
City="Carver"
State="OR"
Zip="97003"/>
</updg:after>
</updg:sync>
</ROOT>
This code inserts a new record into the MEMBERS table of the Oregon Lunar Society (OLS). For this code to work without an explicit mapping schema, the MEMBERS element must map to the MEMBERS table in the OLS database, and the attributes specified in the <after> block must map to the columns of the MEMBERS table. In an insert operation, the empty <before> block is optional. You can leave it out if you want to.
UPDATING A RECORD IN AN SQL DATABASE FROM AN ELEMENT OF AN XML DOCUMENT
Here’s an example of using an updategram to modify the information in an existing SQL table:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<OLS.MEMBERS MemberID="9" />
</updg:before>
<updg:after>
<OLS.MEMBERS Phone="(503)555-5643" />
</updg:after>
</updg:sync>
</ROOT>
This code updates the phone number for the person with MemberID 9. The updategram uses the columns in the <before> block to find the desired record. Because MemberID is the primary key of the MEMBERS table, by itself, it’s sufficient to identify the desired row.
DELETING A RECORD IN AN SQL DATABASE WITH AN UPDATEGRAM
You can also delete one or more records from an SQL table by using an updategram. Here’s an example that deletes two records from the MEMBERS table:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<OLS.MEMBERS MemberID="8"/>
<OLS.MEMBERS MemberID="9"/>
</updg:before>
<updg:after>
</updg:after>
</updg:sync>
</ROOT>
The fact that this updategram has content in its <before> block but an empty <after> block tells you that it’s a delete operation.
Explicit mapping
If you’re using an updategram to make a simple insertion, update, or deletion, implicit mapping using the default schema works well. If, however, you want to perform a complex update, such as inserting records into multiple tables that have a parent–child relationship, you need to specify a mapping schema to make sure that things end up where you want them. The mapping schema should be in the same directory as your updategram; otherwise, you need to specify the path to it.
Two kinds of mapping schema are in use, either of which will work.
CREATING AN UPDATEGRAM WITH AN XSD SCHEMA
XSD stands for XML Schema Definition and is the preferred method of specifying a mapping schema. Following is a mapping schema that maps the <MEMBERS> element to the OLS.MEMBERS table:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="MEMBERS" sql:relation="OLS.MEMBERS" >
<xsd:complexType>
<xsd:attribute name="MemberID"
sql:field="MemberID"
type="xsd:integer" />
<xsd:attribute name="FirstName"
sql:field="FirstName"
type="xsd:string" />
<xsd:attribute name="LastName"
sql:field="LastName"
type="xsd:string" />
<xsd:attribute name="OfficeHeld"
sql:field="OfficeHeld"
type="xsd:string" />
<xsd:attribute name="Email"
sql:field="Email"
type="xsd:string" />
<xsd:attribute name="Phone"
sql:field="Phone"
type="xsd:string" />
<xsd:attribute name="Street"
sql:field="Street"
type="xsd:string" />
<xsd:attribute name="City"
sql:field="City"
type="xsd:string" />
<xsd:attribute name="State"
sql:field="State"
type="xsd:string" />
<xsd:attribute name="Zip"
sql:field="Zip"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
You’d want to save this mapping schema in an appropriately named file — say, MembersUpdateSchema.xml. Next, you’d want to save the following updategram in a file named Member9Updategram.xml in the same directory, as follows:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="MembersUpdateSchema.xml">
<updg:before>
</updg:before>
<updg:after>
<OLS.MEMBERS MemberID="9"
FirstName="Sam"
LastName="Shovel"
OfficeHeld="Investigator"
Email="[email protected]"
Phone="(503)555-8004"
Street="154 Polk St."
City="Carver"
State="OR"
Zip="97003"/>
</updg:after>
</updg:sync>
</ROOT>
That’s it. You’ve specified your mapping schema by using XSD.
CREATING AN UPDATEGRAM WITH AN XDR SCHEMA
XDR, which is short for XML Data Reduced, is an older method of specifying a mapping schema that’s gradually being replaced by XSD (covered in the preceding section). Here’s an XDR schema that’s equivalent to the XSD schema in the preceding section:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="MEMBERS" sql:relation="OLS.MEMBERS" >
<AttributeType name="MemberID" />
<AttributeType name="FirstName" />
<AttributeType name="LastName" />
<AttributeType name="OfficeHeld" />
<AttributeType name="Email" />
<AttributeType name="Phone" />
<AttributeType name="Street" />
<AttributeType name="City" />
<AttributeType name="State" />
<AttributeType name="Zip" />
<attribute type="MemberID" sql:field="MemberID" />
<attribute type="FirstName" sql:field="FirstName" />
<attribute type="LastName" sql:field="LastName" />
<attribute type="OfficeHeld" sql:field="OfficeHeld" />
<attribute type="Email" sql:field="Email" />
<attribute type="Phone" sql:field="Phone" />
<attribute type="Street" sql:field="Street" />
<attribute type="City" sql:field="City" />
<attribute type="State" sql:field="State" />
<attribute type="Zip" sql:field="Zip" />
</ElementType>
</Schema>
The older XDR schema definition language was created by Microsoft and is largely restricted to use with Microsoft products. The XSD schema definition language was created by the W3C international standards body and enjoys widespread use. It is more powerful, but also more complex than XDR.
In these examples, I’ve made the attribute names in the schema the same as the corresponding attribute names in the SQL table. This practice isn’t necessary, however. As long as it’s clear which attributes correspond to which, the attributes can have different names. The same updategram that was created to work with the XSD schema will work with this one, too.
CREATING A MAPPING SCHEMA FOR TABLES WITH A PARENT–CHILD RELATIONSHIP
In “Implicit mapping,” earlier in this chapter, I mention that you don’t really need an explicit mapping schema for a simple update such as the one shown in that section. Providing such a schema does no harm, however. An explicit mapping schema is required, however, for a more complex update, such as insertions into two tables that have a parent–child relationship. Here’s an example of an XSD schema that performs such an update:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="InvoiceToLine"
parent="Sales.Invoice"
parent-key="InvoiceNo"
child="Sales.InvoiceLine"
child-key="InvoiceNo" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Invoice" sql:relation="Sales.Invoice" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Line"
sql:relation="Sales.InvoiceLine"
sql:relationship="InvoiceToLine" >
<xsd:complexType>
<xsd:attribute name="InvoiceNo" type="xsd:integer" />
<xsd:attribute name="ProductID" type="xsd:integer" />
<xsd:attribute name="UnitPrice" type="xsd:decimal" />
<xsd:attribute name="Quantity" type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="CustomerID" type="xsd:string"/>
<xsd:attribute name="InvoiceNo" type="xsd:integer"/>
<xsd:attribute name="InvoiceDate" type="xsd:date"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After you save this schema as InvoiceUpdateSchema.xml, you can reference it with an updategram. The following updategram uses this mapping schema to add a new invoice line record for Invoice 1010:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="InvoiceUpdateSchema.xml" >
<updg:before>
<Invoice InvoiceNo="1010" />
</updg:before>
<updg:after>
<Invoice InvoiceNo="1010" >
<Line ProductID="17" UnitPrice="$5.95"
Quantity="2" />
</Invoice>
</updg:after>
</updg:sync>
</ROOT>
An equivalent XDR schema could look like the following:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Line" sql:relation="Sales.InvoiceLine" >
<AttributeType name="InvoiceNo" />
<AttributeType name="ProductID" />
<AttributeType name="UnitPrice" dt:type="fixed.14.4" />
<AttributeType name="Quantity" />
<attribute type="InvoiceNo" />
<attribute type="ProductID" />
<attribute type="UnitPrice" />
<attribute type="Quantity" />
</ElementType>
<ElementType name="Invoice" sql:relation="Sales.Invoice" >
<AttributeType name="CustomerID" />
<AttributeType name="InvoiceNo" />
<AttributeType name="InvoiceDate" />
<attribute type="CustomerID" />
<attribute type="InvoiceNo" />
<attribute type="InvoiceDate" />
<element type="Line" >
<sql:relationship
key-relation="Sales.Invoice"
key="InvoiceNo"
foreign-key="InvoiceNo"
foreign-relation="Sales.InvoiceLine" />
</element>
</ElementType>
</Schema>
Elementcentric mapping
Elementcentric updategrams, as the name implies, code items as elements. Elements contain child elements, which are the properties of the parent element. The parent element maps to a table, and the child elements map to columns in that table. Here’s an example from the OLS database:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:after>
<OLS.MEMBERS>
<MemberID>5</MemberID>
<FirstName>Gus</FirstName>
<LastName>Roderick</LastName>
<OfficeHeld>Webmaster</OfficeHeld>
<Email>[email protected]</Email>
<Phone>(503)555-9976</Phone>
<Street>43 Ash St.</Street>
<City>Silverton</City>
<State>OR</State>
<Zip>97078</Zip>
</OLS.MEMBERS>
</updg:after>
</updg:sync>
</ROOT>
Because no mapping schema was specified, this updategram uses implicit mapping.
Attributecentric mapping
In attributecentric mapping, the elements have attributes rather than child elements. The following updategram, which also uses implicit mapping, is an example of attributecentric mapping:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<updg:/before>
<updg:after>
<OLS.MEMBERS
MemberID="5"
FirstName="Gus"
LastName="Roderick"
OfficeHeld="Webmaster"
Email="[email protected]"
Phone="(503)555-9976"
Street="43 Ash St."
City="Silverton"
State="OR"
Zip="97078"/>
</OLS.MEMBERS>
</updg:after>
</updg:sync>
</ROOT>
Mixed elementcentric and attributecentric mapping
It’s possible to mix elementcentric and attributecentric mapping in the same updategram, although why you would want to do so is beyond me. The difference between the two approaches can lead to confusion. Anyway, here’s an example:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<updg:/before>
<updg:after>
<OLS.MEMBERS
MemberID="5"
FirstName="Gus"
LastName="Roderick"
OfficeHeld="Webmaster"
Email="[email protected]"
Phone="(503)555-9976">
<Street>43 Ash St.</Street>
<City>Silverton</City>
<State>OR</State>
<Zip>97078</Zip>
</OLS.MEMBERS>
</updg:after>
</updg:sync>
</ROOT>
Once again, this code uses implicit mapping.
Schemas that allow null values
Sometimes, the updategram you’re using to insert values into an SQL table may not have a value for each of the table’s columns. In such a case, you want to put a null value in the columns for which no value is specified. This issue arises because XML, like most computer languages other than SQL, doesn’t support null values. You can handle this problem by assigning the xsi:nil attribute to any element in the updategram that may contain a null value. In the corresponding XSD schema, you must specify the XSD nillable attribute. Here’s an example of such a schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="MEMBERS" sql:relation="OLS.MEMBERS" >
<xsd:complexType>
<xsd:attribute name="MemberID"
sql:field="MemberID"
type="xsd:integer" />
<xsd:attribute name="FirstName"
sql:field="FirstName"
type="xsd:string" />
<xsd:attribute name="LastName"
sql:field="LastName"
type="xsd:string" />
<xsd:all>
<xsd:element name="OfficeHeld"
sql:field="OfficeHeld"
type="xsd:string"
nillable="true"/>
</xsd:all>
<xsd:attribute name="Email"
sql:field="Email"
type="xsd:string" />
<xsd:attribute name="Phone"
sql:field="Phone"
type="xsd:string" />
<xsd:attribute name="Street"
sql:field="Street"
type="xsd:string" />
<xsd:attribute name="City"
sql:field="City"
type="xsd:string" />
<xsd:attribute name="State"
sql:field="State"
type="xsd:string" />
<xsd:attribute name="Zip"
sql:field="Zip"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
A member of the OLS may not hold any office, so the OfficeHeld element is designated as nillable. Here’s an example of an updategram that uses this schema:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="MembersUpdateSchema.xml">
<updg:before>
</updg:before>
<updg:after>
<OLS.MEMBERS MemberID="3"
FirstName="Tom"
LastName="Charges"
Email="[email protected]"
Phone="(503)555-3211"
Street="132 22<sup>nd</sup> St."
City="Portland"
State="OR"
Zip="97245">
<OfficeHeld xsi:nil="true">
</OfficeHeld>
</OLS.MEMBERS>
</updg:after>
</updg:sync>
</ROOT>
With the schema used here, records can be inserted into the MEMBERS table from an updategram if the OfficeHeld attribute is absent, but that’s not true for any of the other attributes. All the other attributes must contain definite values.