Registering XML Schema in DB2 9 Using Visual Studio 2005

In early June 2006, IBM announced the next release of the DB2 UDB product, DB2 9. Part of this announcement includes the support for Microsoft Visual Studio 2005 and its accompanying ADO.NET 2.0 driver.

As you may recall, Microsoft announced the Visual Studio 2005 product, along with SQL Server 2005, in late 2005. Around the same time, IBM delivered a beta of the now officially announced DB2 integration into Microsoft Visual Studio 2005 and ADO.NET 2.0.

For this article, I recommend that you download the free DB2 Express-C product, which includes the pureXML feature, so you can follow the steps outlined in this article. You can download your own free no-database-size-limit copy of DB2 9 (which also includes 64-bit support) at: http://www.ibm.com/db2/express.

As I’ve been writing about the DB2 integration into Visual Studio 2005, I’ve discussed the following topics in other articles:

In this article, I want to show you how you can leverage the Visual Studio 2005 XML tools to register the XSD document created in the previous article in this series. You will use this document in a future article when I cover how to insert and work with XML data in DB2 9.

Note: This article series assumes that you have created the DB2 9 SAMPLE database using the following command: db2sampl –sql –xml. It also assumes that you have successfully added a connection to this database in the Visual Studio 2005 Server Explorer. For information about how to add a DB2 database connection to the Server Explorer, see “Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer” at the end of this article.

A Bit about Support for XML Schema Definitions in DB2 9

DB2 9 comes with a rich and flexible set of services for working with XSD documents. First and foremost, it should be noted that you don’t even need to have an XML Schema in order to store an XML document in DB2 9.

In DB2 9, the only prerequisite for storing an XML document using the pureXML feature is that the XML document be well formed. Although it’s outside the scope of this article to detail XML standards, a document is generally considered to be well formed if it has the following characteristics:

1. It has exactly one root element. For example:

Well formed:

<person>
  <fname>Mike</fname><lname>Kelly</lname>
</person>

Not well formed:

  <fname>Mike</fname><lname>Kelly</lname>

2. Each opening tag is matched by a closing tag. For example:

Well formed:

  <fname>Mike</fname><lname>Kelly</lname>

Not well formed:
```
  <fname>Mike<lname>Kelly</fname>
```

3. All elements are properly nested. For example:

Well formed:

<person>
  <fname>Mike</fname><lname>Kelly</lname>
</person>

Not well formed:

  <fname>Mike</lname><lname>Kelly</fname>

4. All attribute values are in quotes. For example:

Well formed:
```
<person id="3431321">
```
Not well formed:
```
<person id=3431321>
```

5. The XML document does not contain any special or reserved keywords. For example:

Well formed

<comments>Quantity on hand is &lt;5</comments>

Not well formed:

<comments>Quantity on hand is <5</comment>

Fore more information about what constitutes a well-formed document, see: http://www.w3.org/TR/REC-xml. This is an important concept because if your document is not well-formed, you can’t store it in a pureXML column in DB2 9. While this requirement applies to documents stored in pureXML columns, you can still store schema-less XML documents in DB2 9. This adds to the flexibility and wide range of persistence services that DB2 9 provides for XML.

XML is all about flexibility. DB2 9, as you’ll find out the more you work with it, is pretty much the only relational database on the market today that really delivers on the flexibility promise of XML. For example, some other vendors require you to store your XML documents alongside a relational column while others always require an XML Schema to store XML.

You can associate different schemas with different columns in a table in DB2 9. This is yet another key flexibility point (again, central to XML) that isn’t shared by other vendors in their current implementation. For example, consider a wealth management company that’s involved in financial transactions for its clients around the globe. In the customer profile database, perhaps some clients actively trade while others are actively managed; each client type has a different process for its activities, and so on. If you had a different XML specification for each client type, in DB2 9, you can keep all your clients in one table, as opposed to having to separate them into different tables, since you can reference different XSDs in a single column.

But the flexibility of the DB2 9 XML Schema Repository (XSR) doesn’t stop there; perhaps the most important flexible design point is the fact that DB2 9 supports schema evolution. Schema evolution is a key concept to XML because it’s the basis of flexibility for which people choose XML.

For example, imagine that you have a set of clients each validating against the FpML v1.1 protocol, while others are at v1.2. How do you support these clients? Your database first has to support multiple schemas so that eliminates some vendors. But some vendors that support multiple schemas don’t allow their components to intersect, which means they don’t support schema evolution. DB2 9 allows you to support multiple editions and versions of those editions of XSDs to provide the utmost flexibility. After all, does everyone in your company, let alone your supply chain or partner companies, upgrade their software at the same time?

One final thing about validation – it’s a very useful and important feature when working with XML, but you should only use it when you need to use it. Validation of XML documents during insertion is a resource-intensive operation so it should be used only when needed. For example, you don’t encrypt data that isn’t subject to more stringent security requirements, do you? Keep this in mind when working with your XML documents.