Registering XML Schema in DB2 9 Using Visual Studio 2005

Thursday Nov 9th 2006 by Paul Zikopoulos

Learn how to leverage the Visual Studio 2005 XML tools to register the XSD document created in the previous article in this series.

In a previous series of articles, I showed you the integration features between the IBM DB2 Universal Database for Linux, UNIX, and Windows Version 8.x (DB2 UDB) product and the Microsoft Visual Studio.NET 2003 integrated development environment (IDE).

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.

The beta-version of this code became generally available when DB2 9 made its debut in August 2006, and you can download it at: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=dbaddvs&S_TACT=105AGX11&S_CMP=TILE=.

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:
  • Not well formed:

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

  • Well formed:
  • Not well formed:

3.  All elements are properly nested. For example:

  • Well formed:
  • Not well formed:

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.

Registering an XML Schema Definition in DB2 9 using Visual Studio 2005

In order to take full advantage of the powerful capabilities of the pureXML feature in DB2 9, you need to register your XSDs in the DB2 9 XSR. Once an XSD is registered in the XSR, you can optionally validate all incoming XML documents with the XSD document or code business logic in the database to perform validation.

To register the Customer XSD that you created in the last article of this series, perform the following steps:

1.  Right-click XML Schema Repository and select Register XML Schema:

The XSD Registration designer opens:

2.  Click the ellipsis () and navigate to the path where you saved the Customer XSD document in the previous article, and click Open.

When you do this, the XSR name field and the associated dependency on any XML Schemas within the selected XSD document (in this case, the CanadianAddress.xsd, which defines the complex CanadianAddress data type) are automatically filled in for you as shown below:

3.  Press Ctrl+S to register the schema when you have focus on the XSD designer. If you’re registering this XSD document in the SAMPLE database created with the options outlined at the start of this article, you should receive the following error:

If you created the SAMPLE database with the XML option, then you will receive this error because an XSD document with the same name is registered in this database as part of the SAMPLE database creation process:

I included this error in this article to illustrate that fact that you can only have a single instance of an XML Schema name for a particular schema in the database, although different names can contain versions of the same schema.

One of the key capabilities of the XSR support in DB2 9 is the ability to support evolving schemas. Let’s assume that the schema we created in the previous article was an evolution of a schema that already existed in the database. In this case, you could change the registration of the schema by calling it by a different name and mapping it to the underlying file name, as shown below:

Once you have registered your XSD document it should show up in the Server Explorer window, at which time you can perform multiple actions on it from the Visual Studio IDE:

Wrapping it Up...

In this article, I showed you how to register an XML Schema Definition document in the DB2 9 XML Schema Repository without leaving Visual Studio 2005. This demonstrates the rich functionality provided by the IBM Database add-ins for Visual Studio 2005. This free download keeps productivity high in a .NET environment.

Of course, there are many ways to register schemas in DB2 9. For example, you could use the new DB2 9 Developer Workbench (an Eclipse-based development tool):


Or, you can use the following syntax in the command line processor (CLP):

In my next article, I’ll show you how to create a table that uses an XML column and an index specifically designed for searching the XML column using an XML pattern expression – all without ever leaving your Visual Studio 2005 IDE.

Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer

If you’re familiar with the Visual Studio.NET 2003 support that DB2 UDB provides, you’ll recall that .NET developers writing applications on the DB2 platform were required to work within the IBM Explorer. The IBM Explorer was functionally equivalent to the Server Explorer; however, there were no open interfaces into the Server Explorer in Visual Studio.NET 2003 by which DB2 UDB could leverage to provide some of the features unique to the DB2 UDB plug-in.

The architecture of Visual Studio 2005 changed such that there are now interfaces that let you develop applications that connect to DB2 UDB V8 and DB2 9 databases using the Server Explorer. This provides a more native experience for .NET application developers used to developing applications on SQL Server databases.

To add a DB2 database connection to your Server Explorer, perform the following steps:

Note: If you already have a database connection to the that database you want to provide your ASP.NET Web site with data, you can skip this section.

1.  Right-click the Database Connections folder in the Server Explorer and select the Add Connection option. The Add Connection dialog box opens:

2.  Ensure that the Data source field points to the (.NET Framework Data Provider for IBM DB2) data provider so that the Server Explorer will use the ADO.NET data provider written by IBM specifically for DB2 UDB and DB2 9 databases.

The DB2 ADO.NET provider is not the default provider shown in this field. To change the database provider to use the one for DB2, click Change and select the IBM DB2 option from the Data source list as shown below (you should also ensure that the IBM DB2 Data Provider for .NET Framework is selected in the Data provider field, but this should be the default):

Note: If you plan to frequently work with DB2 database connections, I recommend you select the Always use this selection check box. When you add another database connection, Visual Studio 2005 will automatically select the DB2 data provider if this option is selected.

3.  Enter the server name and port number (separated by a : ) in the Enter server name field. If you are connecting to a local database, you can use the localhost alias for your workstation.

Depending on the version of DB2 that you are running your beta on, you can optionally click Refresh to automatically enumerate all the databases configured to respond to DB2 network database identification requests and automate this process.

4.  Enter your user account credentials in the User ID and Password fields. I recommend you save these credentials in the connection string (they are encrypted) by selecting Save my password. Selecting this option makes application development more streamlined, as you are not challenged to provide authentication details during subsequent access requests to the DB2 database.

5.  Select the database name from the Select or enter a database name drop-down list, or enter the name manually.

Note: In this article, I chose to connect to the SAMPLE database that is shipped with DB2 UDB V8. If you don’t have the SAMPLE database created on your workstation, you can create it now by entering the db2sampl command from a Windows-based command prompt.

6.  Optionally use the Specify Connection Options and Specify Filtering Options sections to further customize your database connection. The options associated with these toggles are shown below:

The DB2 support for Visual Studio 2005 comes with a rich set of connection time and filtering options. For the purposes of this article, you can just select the defaults.

7.  Test the connection using the Test Connection button.

8.  Click OK.

After adding your database connection, the Visual Studio 2005 Server Explorer should look similar to the following:

In the previous figure, you can see that I’ve expanded the SAMPLE database connection. Below this database connection is a connection object to a SQL Server 2005 database. Notice the beside this database connection object: all databases appear this way until you click them to make the database connection.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 and has written over sixty magazine articles and several books about it. Paul has co-authored the books: Information on Demand: Introduction to DB2 9 New Features, IBM DB2 9: New Features, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, DB2 Universal Database, and pureXML are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2006. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Mobile Site | Full Site