XML is nothing new. But thanks to new programming techniques like Ajax, its uses have grown tremendously over the recent years. But strangely enough, databases have had few, if any, native XML features—until now.
IBM's newest database, IBM DB2 9, boasts pureXML, a collection of native XML features that alleviate much of the stress of working with XML. From the new XML data type to the XQuery language, DB2 9 makes life easier for developers working with XML, which, at this point, is most people.
As much as DBAs would like it to be so, data doesn't always arrive at our doorstep neatly organized and labeled like memos in a mail bin at work. Sometimes you get the oddly shaped package that defies classification. For that, we create catch-all fields like "Notes" or "Comments" or "Misc." These are usually CLOB fields, large enough to hold any kind of random text that needs to be dumped in there.
 | |
| Figure 1. XML Extender |
You'll see an example of this in the following walkthrough—some information is standard enough to warrant its own data column, but other information is unique to each individual row. Herein lies the advantage of using an XML data field.
CLOB fields can hold anything—text, XML, HTML, customized descriptors. But to a database, it's all just text. That makes it hard to search, hard to parse, and very hard to format if and when you get the correct sub-data out of your CLOB fields.
XML fields, however, treat their content as XML, not as text that happens to have brackets in it. As a result, the data you store in an XML field remains highly usable. It's like having a mini-data table within your data table. The XML data can be searched using SQL, XMLSQL, XQuery, or some combination of all three. You can retrieve specific data nodes rather than simply the surrounding text, which may or may not be relevant. You can even associate an XML document, which is unique to each row, with strict formatting such as an XSD or DTD.
 | |
| Figure 2. Launch Add-Ins Installation |
And most importantly for your purposes here, XML data types work brilliantly with Ajax, or any other application that requires working directly with XML. Simply put, it takes the headache out of building XML-based applications.
This walkthrough shows you, step-by-step, how to use some of these new features to your advantage. You'll begin by downloading DB2 9 Express-C, which is distributed free. You'll install it and the new DB2 9 Visual Studio Add-ins, then use both to create a new Superhero database. After populating the database with sample data, including some pre-written XML documents, you'll create C# Web Services in Visual Studio 2005 that demonstrate the advantages of the XML data type. Lastly, you'll build out the HTML, JavaScript, and CSS using more pre-written code. By the time you finish, you'll have created a working app that lists your current Hero roster, triggers an asynchronous JavaScript call to your Web Service when you click on a Hero, retrieves data directly from an XML field, and dynamically generates new DOM objects based on that data.
 | |
| Figure 3. Create Your Own Database |
The technologies covered here include:
As you finish the installation, you should see an option to continue with the install of the DB2 Add-ins for Visual Studio 2005, as in Figure 2. Select that—those tools will come in handy later in the walkthrough. If for some reason you miss it, you can re-run setup and choose either "DB2 Client," which also installs the add-ins, or "IBM Database add-ins for Visual Studio 2005."
 | |
| Figure 4. Enable Database for XML |
After installing Express-C, launch First Steps if it hasn't already launched for you (Start -> IBM DB2 -> DB2 (or the name you specified for this copy of DB2) -> Set-up Tools -> First Steps). For this walkthrough, you don't need to install the SAMPLE database, though it wouldn't hurt if you want to test your installation. In any case, select "Create your own database," as in Figure 3. The following section will walk you through creating the database and necessary tables, as well as populating them with sample data.
Name the database "AJAXDEMO." Check the option to "Enable database for XML." Depending on your build, you may see additional options, such as those included in Figure 4.
Once you've created the database, close First Steps and open up Visual Studio. Now's a good time to test out your new add-ins.
 Figure 5. New Web Site |
 Figure 6. ASP.NET HTTP C# Web Site |
 | |
| Figure 7. Visual Studio Add-ins |
To test your new Web app, type something between the <div> tags on your Default.aspx page and run it. If everything checks out, stop execution and go back to the IDE. Under Tools, look at "IBM DB2 Tools" (Figure 7). Basically, you have access to all of your DB2 products through this menu, just as you would the "Start -> Programs" menu. Select Tools -> IBM DB2 Tools -> Command Editor to open up a query window.
Copy the SQL code from Listing 1 below and paste it into your Command Editor window.
Listing 1: Table Creation Code
CONNECT TO AJAXDEMO;
------------------------------------------------
-- DDL Statements for table "NULLID "."HEROES_NOXML"
------------------------------------------------
CREATE TABLE "NULLID "."HEROES_NOXML" (
"HEROID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"HERONAME" VARCHAR(50) NOT NULL ,
"POWER" VARCHAR(255) ,
"WEAKNESS" VARCHAR(255) ,
"NOTES" CLOB(1048576) LOGGED NOT COMPACT )
IN "USERSPACE1" ;
-- DDL Statements for primary key on Table "NULLID "."HEROES_NOXML"
ALTER TABLE "NULLID "."HEROES_NOXML"
ADD CONSTRAINT "CC1158547603783" PRIMARY KEY
("HEROID");
------------------------------------------------
-- DDL Statements for table "NULLID "."HEROES_XML"
------------------------------------------------
CREATE TABLE "NULLID "."HEROES_XML" (
"HEROID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"HERONAME" VARCHAR(50) NOT NULL ,
"NOTES" XML )
IN "USERSPACE1" ;
-- DDL Statements for primary key on Table "NULLID "."HEROES_XML"
ALTER TABLE "NULLID "."HEROES_XML"
ADD CONSTRAINT "CC1158547736768" PRIMARY KEY
("HEROID");
COMMIT WORK;
CONNECT RESET;
TERMINATE; This creates the following two tables:
HEROES_NOXML:
HEROID, INTEGER (4), IDENTITY, NOT NULLABLE
HERONAME, VARCHAR (50), NOT NULLABLE
POWER, VARCHAR (255), NULLABLE
WEAKNESS, VARCHAR (255), NULLABLE
NOTES, CLOB, NULLABLEHEROID, INTEGER (4), IDENTITY, NOT NULLABLE
HERONAME, VARCHAR (50), NOT NULLABLE
NOTES, XML, NULLABLEPopulate the first table by copying the SQL code from Listing 2 into your Command Editor.
Listing 2: HEROES_NOXML Data
INSERT INTO NULLID.HEROES_NOXML
(HERONAME, POWER, NOTES)
VALUES ('Mockingbird','Can mimic any sound she hears.','Alter ego:
Mariah Carey');
INSERT INTO NULLID.HEROES_NOXML
(HERONAME, POWER, WEAKNESS, NOTES)
VALUES ('Indigo','Telepathy','Gets cranky if he stays up too late.','Current age: 8.
Additional weakness: lactose intolerant.');
INSERT INTO NULLID.HEROES_NOXML
(HERONAME, POWER, WEAKNESS, NOTES)
VALUES ('Indivisibull','Super strength; can reattach severed body
parts.','Not too bright.','Secret origin: a somewhat clumsy man,
Hank "Bullhead" Durham fell into a vat of fresh glue
and was promptly struck by lightning. Since then, whenever he
loses a limb, he has simply to hold it in place and the limb becomes reattached.');
INSERT INTO NULLID.HEROES_NOXML
(HERONAME, POWER, WEAKNESS, NOTES)
VALUES ('Dayglo','Glows under a black light.','Not having a black light around.','Usually
relegated to sidekick, though once played a crucial role in a mission busting an
underground pot growers cabal.');
INSERT INTO NULLID.HEROES_NOXML
(HERONAME, POWER, WEAKNESS, NOTES)
VALUES ('Thumbalina','Ability to text message at light speed.','Thumbsucking
(a nervous habit in times of great stress).','Former codename: Twiddle.'); Populating an XML field requires some special scripting, so you'll get to the second table in a Part 2 .