IDS 11 purifies XML

Jean Georges Perrin As an application developer, I love Informix Dynamic Server (IDS) 11 (which was code-named "Cheetah" before its release). Why get so excited? Because "Cheetah" brings tools that make my day-to-day life easier. I also love XML. For me, XML is the sign of a new generation of IT professionals. It's the clear separation between a generation focusing on don’t-waste-that-extra-byte and a generation focusing on making IT understandable by its peers.

And now, my favorite engine embraces my favorite language. In my article on DB2mag.com, I'll show you how my company, GreenIvory, uses XML and IDS 11 for Web publishing, and how these technologies can benefit your company, too. You'll learn what happens when Cheetah speaks XML and how it can work to your benefit.

What is XML anyway?

XML stands for eXtended Markup Language. It's often a text file; it can also be a stream, compressed, “binary’zed”, encrypted, and tortured in many other ways, but XML is (and remains) textual information (see. Figure 1)


Figure 1. An XML document

Now you know about all that should be known about XML. Of course, you can still learn about XSL, XPath, namespaces, XML Schema, DTD, and plenty of other acronyms, but you’ll do that on your spare time.

XML Activation

Once you've installed Cheetah, there's nothing more to do. You simply need to tell IDS that you're going to use the XML virtual processor (VP). To do so, edit the ONCONFIG file, and add:

VPCLASS idsxmlvp

You can have several VPCLASS lines in your ONCONFIG file, if needed. Typically, you could find one for Krakatoa, the Java-in-the-engine extension.

Once you have restarted your engine, you can check that everything is fine:

onstat -g glo

should return:

vp pid class usercpu syscpu total
3 3460 idsxmlvp 0.00 0.00 0.00
4 2820 jvp 0.00 0.00 0.00

Three IDS Families

IDS comes with nine functions divided into three families.

Each function has its character large object (CLOB) counterpart: The “base” function will return LVARCHAR(32739) and the CLOB version of the function will return a CLOB (okay, that was easy). Example: genXML() returns a LVARCHAR(32739); genXMLCLOB() returns a CLOB.

To make examples more appealing and more attractive, I have manually indented them.

IDS Generations

The first family is XML publishing & generation. They help you get the data from IDS in an XML format. All those functions are prefixed by “gen”.

The functions in this family are:

genXML ( root_element , rows )
genXMLCLOB ( root_element , rows )
genXMLElem ( row , element )
genXMLElemCLOB ( row , element )
genXMLQueryHdr ( row , query )
genXMLQueryHdrCLOB ( row , query )
genXMLQuery ( row , query )
genXMLQueryCLOB ( row , query )
genXMLSchema ( row , element )
genXMLSchemaCLOB ( row , element )

genXML

This function returns an XML representation of the result set. Note that there is no root element.


SELECT genXML(customer, "customer-row") FROM customer WHERE city="Menlo Park";


<customer-row 

  customer_num="113" 

  fname="Lana           " 

  lname="Beatty         " 

  company="Sportstown          " 

  address1="654 Oak Grove       " 

  city="Menlo Park     " 

  state="CA" 

  zipcode="94025" 

  phone="415-356-9982      "/>

<customer-row 

  customer_num="115" 

  fname="Alfred         " 

  lname="Grant          " 

  company="Gold Medal Sports   " 

  address1="776 Gary Avenue     " 

  city="Menlo Park     " 

  state="CA" 

  zipcode="94025" 

  phone="415-356-1123      "/>

Notice that the white spaces are kept as the fields are mostly of type CHAR(xx).

genXMLElem

genXMLElem behaves in a very similar way to genXML. The main difference is that it cuts field into element, whereas genXML uses attributes. genXMLElem also creates a root element.

SELECT genXMLElem(customer, "my-customers") 
FROM customer WHERE customer_num="114" OR customer_num="112";


<my-customers>

  <row>

    <customer_num>114</customer_num>

    <fname>Frank          </fname>

    <lname>Albertson      </lname>

    <company>Sporting Place      </company>

    <address1>947 Waverly Place   </address1>

    <city>Redwood City   </city>

    <state>CA</state>

    <zipcode>94062</zipcode>

    <phone>415-886-6677      </phone>

  </row>

  <row>

    <customer_num>112</customer_num>

    <fname>Margaret       </fname>

    <lname>Lawson         </lname>

    <company>Runners & Others    </company>

    <address1>234 Wyandotte Way   </address1>

    <city>Los Altos      </city>

    <state>CA</state>

    <zipcode>94022</zipcode>

    <phone>415-887-7235      </phone>

  </row>

</my-customers>

genXMLQuery

This function creates an XML document from a SQL query.


EXECUTE FUNCTION genXMLQuery(
'my-customers', 
'SELECT customer_num, lname FROM customer WHERE fname MATCHES "Frank"');


<my-customers>

  <row>

    <customer_num>114</customer_num>

    <lname>Albertson      </lname>

  </row>

  <row>

    <customer_num>128</customer_num>

    <lname>Lessor         </lname>

  </row>

</my-customers>

genXMLQueryHdr

Same idea as the previous function, but genXMLQueryHdr creates a more sophisticated XML document, taking the encoding from the database.

EXECUTE FUNCTION genXMLQueryHdr(
'my-customers', 
'SELECT customer_num, lname, trim(city) AS city FROM customer 
WHERE fname MATCHES "Fran*"');

Note the result of the trim() function: for its non-XML use, it removes the extra white spaces.


<?xml version="1.0" encoding="en_US.819" ?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<my-customers>

  <row>

    <customer_num>111</customer_num>

    <lname>Keyes          </lname>

    <city>Sunnyvale</city>

  </row>

  <row>

    <customer_num>114</customer_num>

    <lname>Albertson      </lname>

    <city>Redwood City</city>

  </row>

  <row>

    <customer_num>128</customer_num>

    <lname>Lessor         </lname>

    <city>Phoenix</city>

  </row>

</my-customers>

genXMLSchema

This function generates a complete XML Schema from the table structure.

SELECT genXMLSchema(customer, "my-customers") 
FROM customer 
WHERE phone MATCHES "408-7*";


<?xml version="1.0" encoding="en_US.819" ?>

xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"

targetNamespace="http://www.ibm.com"

xmlns="http://www.ibm.com"

ElementFormDefault="qualified">

<xs:element name="my-customers">

  <xs:complexType>

    <xs:sequence>

      <xs:element name="customer_num"  type="xs:serial"/>

      <xs:element name="fname"  type="xs:char(15)"/>

      <xs:element name="lname"  type="xs:char(15)"/>

      <xs:element name="company"  type="xs:char(20)"/>

      <xs:element name="address1"  type="xs:char(20)"/>

      <xs:element name="city"  type="xs:char(15)"/>

      <xs:element name="state"  type="xs:char(2)"/>

      <xs:element name="zipcode"  type="xs:char(5)"/>

      <xs:element name="phone"  type="xs:char(18)"/>

    </xs:sequence>

  </xs:complexType>

</xs:element>

</xs:schema>



<my-customers>

  <row>

    <customer_num>101</customer_num>

    <fname>Ludwig         </fname>

    <lname>Pauli          </lname>

    <company>All Sports Supplies </company>

    <address1>213 Erstwild Court  </address1>

    <city>Sunnyvale      </city>

    <state>CA</state>

    <zipcode>94086</zipcode>

    <phone>408-789-8075      </phone>

  </row>

  <row>

    <customer_num>109</customer_num>

    <fname>Jane           </fname>

    <lname>Miller         </lname>

    <company>Sport Stuff         </company>

    <address1>Mayfair Mart        </address1>

    <address2>7345 Ross Blvd.     </address2>

    <city>Sunnyvale      </city>

    <state>CA</state>

    <zipcode>94086</zipcode>

    <phone>408-723-8789      </phone>

  </row>

</my-customers>

Note: with Cheetah xc1 (IDS v11.10.xc1) there is a small issue in the header. Right now, it produces "xs:schema" and it should produce "<xs:schema". The IBM team told me this issue will be corrected in Fix Pack 2.

Extraction

The second family of functions is about XML query & extraction. Basically, you get information in XML based on an XML query. These functions are prefixed by “extract:”

extract ( xml_string , xpath_expression )
extractXMLCLOB ( xml_string , xpath_expression )
extractValue ( xml_string , xpath_expression )
extractXMLCLOBValue ( xml_string , xpath_expression )

The CLOB syntax changed a bit, but you've got the idea, right? extract() returns a LVARCHAR(32739) and extractXMLCLOB() returns a CLOB.

Both functions work on XML content, so you need to have some XML already in your database, which isn't really the case with our favorite stores_demo… So, you need to modify it a bit…

CREATE TABLE xmlcustomer (xmlcol LVARCHAR(24000)); INSERT INTO xmlcustomer VALUES ("<my-customers><row> <customer_num>101</customer_num><fname>Ludwig</fname> <lname>Pauli</lname><company>All Sports Supplies </company><address1>213 Erstwild Court</address1> <city>Sunnyvale</city><state>CA</state><zipcode>94086 </zipcode><phone>408-789-8075</phone></row><row> <customer_num>102</customer_num><fname>Carole</fname> <lname>Sadler</lname><company>Sports Spot </company><address1>785 Geary St</address1> <city>San Francisco</city><state>CA</state><zipcode> 94117</zipcode><phone>415-822-1289</phone></row><row> <customer_num>103</customer_num><fname>Philip</fname> <lname>Currie</lname><company>Phil's Sports </company><address1>654 Poplar</address1> <address2>P. O. Box 3498</address2> <city>Palo Alto</city><state>CA</state> <zipcode>94303</zipcode><phone>415-328-4543</phone> </row><row> <customer_num>104</customer_num><fname> Anthony</fname><lname>Higgins</lname><company>Play Ball!</company><address1>East Shopping Cntr.</address1><address2>422 Bay Road</address2> <city>Redwood City</city><state>CA</state> <zipcode>94026</zipcode><phone>415-368-1100</phone></row><row> <customer_num>105</customer_num><fname>Raymond</fname> <lname>Vector</lname><company>Los Altos Sports </company><address1>1899 La Loma Drive</address1> <city>Los Altos</city><state>CA</state><zipcode>94022 </zipcode><phone>415-776-3249</phone></row><row> <customer_num>106</customer_num><fname>George</fname> <lname>Watson</lname><company>Watson& Son</company> <address1>1143 Carver Place</address1><city>Mountain View</city><state>CA</state><zipcode>94063</zipcode> <phone>415-389-8789</phone></row><row> <customer_num>107</customer_num><fname>Charles</fname> <lname>Ream</lname><company>Athletic Supplies</company> <address1>41 Jordan Avenue</address1><city>Palo Alto </city><state>CA</state><zipcode>94304</zipcode> <phone>415-356-9876</phone></row><row> <customer_num>108</customer_num><fname>Donald</fname> <lname>Quinn</lname><company>Quinn's Sports</company> <address1>587 Alvarado</address1><city>Redwood City </city><state>CA</state><zipcode>94063</zipcode> <phone>415-544-8729</phone></row><row> <customer_num>109</customer_num><fname>Jane </fname> <lname>Miller</lname><company>Sport Stuff</company> <address1>Mayfair Mart</address1><address2>7345 Ross Blvd.</address2><city>Sunnyvale</city><state>CA </state><zipcode>94086</zipcode><phone>408-723-8789 </phone></row><row> <customer_num>110</customer_num><fname>Roy</fname> <lname>Jaeger</lname><company>AA Athletics</company> <address1>520 Topaz Way</address1><city>Redwood City </city><state>CA</state><zipcode>94062</zipcode> <phone>415-743-3611</phone></row><row> <customer_num>111</customer_num><fname>Frances</fname> <lname>Keyes</lname><company>Sports Center</company> <address1>3199 Sterling Court</address1><city>Sunnyvale </city><state>CA</state><zipcode>94085</zipcode> <phone>408-277-7245</phone></row><row> <customer_num>112</customer_num><fname>Margaret </fname><lname>Lawson</lname><company>Runners & Others</company><address1>234 Wyandotte Way</address1> <city>Los Altos</city><state>CA</state><zipcode>94022 </zipcode><phone>415-887-7235</phone></row><row> <customer_num>113</customer_num><fname>Lana</fname> <lname>Beatty</lname><company>Sportstown</company> <address1>654 Oak Grove</address1><city>Menlo Park </city><state>CA</state><zipcode>94025</zipcode> <phone>415-356-9982</phone></row><row> <customer_num>114</customer_num><fname>Frank</fname> <lname>Albertson</lname><company>Sporting Place </company><address1>947 Waverly Place</address1><city> Redwood City</city><state>CA</state><zipcode>94062 </zipcode><phone>415-886-6677</phone></row><row> <customer_num>115</customer_num><fname>Alfred</fname> <lname>Grant</lname><company>Gold Medal Sports </company><address1>776 Gary Avenue</address1><city> Menlo Park</city><state>CA</state><zipcode>94025 </zipcode><phone>415-356-1123</phone></row><row> <customer_num>116</customer_num><fname>Jean</fname> <lname>Parmelee</lname><company>Olympic City</company> <address1>1104 Spinosa Drive</address1><city>Mountain View</city><state>CA</state><zipcode>94040</zipcode> <phone>415-534-8822</phone></row><row> <customer_num>117</customer_num><fname>Arnold</fname> <lname>Sipes</lname><company>Kids Korner</company> <address1>850 Lytton Court</address1><city>Redwood City</city><state>CA</state><zipcode>94063</zipcode> <phone>415-245-4578</phone></row><row> <customer_num>118</customer_num><fname>Dick</fname> <lname>Baxter</lname><company>Blue Ribbon Sports </company><address1>5427 College</address1><city> Oakland</city><state>CA</state><zipcode>94609 </zipcode><phone>415-655-0011</phone></row><row> <customer_num>119</customer_num><fname>Bob</fname> <lname>Shorter</lname><company>The TriathletesClub </company><address1>2405 Kings Highway</address1><city> Cherry Hill</city><state>NJ</state><zipcode>08002 </zipcode><phone>609-663-6079</phone></row><row> <customer_num>120</customer_num><fname>Fred</fname> <lname>Jewell</lname><company>Century Pro Shop </company><address1>6627 N. 17th Way</address1><city> Phoenix</city><state>AZ</state><zipcode>85016 </zipcode><phone>602-265-8754</phone></row><row> <customer_num>121</customer_num><fname>Jason</fname> <lname>Wallack</lname><company>City Sports</company> <address1>Lake Biltmore Mall</address1><address2> 350 W. 23rd Street</address2><city>Wilmington</city> <state>DE</state><zipcode>19898</zipcode><phone> 302-366-7511</phone></row><row> <customer_num>122</customer_num><fname>Cathy</fname> <lname>O'Brian</lname><company>The Sporting Life </company><address1>543 Nassau Street</address1><city> Princeton</city><state>NJ</state><zipcode>08540 </zipcode><phone>609-342-0054</phone></row><row> <customer_num>123</customer_num><fname>Marvin</fname> <lname>Hanlon</lname><company>Bay Sports</company> <address1>10100 Bay Meadows Ro</address1><address2> Suite 1020</address2><city>Jacksonville</city><state> FL</state><zipcode>32256</zipcode><phone>904-823-4239 </phone></row><row> <customer_num>124</customer_num><fname>Chris</fname> <lname>Putnum</lname><company>Putnum's Putters </company><address1>4715 S.E. Adams Blvd</address1> <address2>Suite 909C</address2><city>Bartlesville </city><state>OK</state><zipcode>74006</zipcode> <phone>918-355-2074</phone></row><row> <customer_num>125</customer_num><fname>James</fname> <lname>Henry</lname><company>Total FitnessSports </company><address1>1450 Commonwealth Av</address1> <city>Brighton</city><state>MA</state><zipcode>02135 </zipcode><phone>617-232-4159</phone></row><row> <customer_num>126</customer_num><fname>Eileen</fname> <lname>Neelie</lname><company>Neelie's DiscountSp </company><address1>2539 South Utica Str</address1> <city>Denver</city><state>CO</state><zipcode>80219 </zipcode><phone>303-936-7731</phone></row><row> <customer_num>127</customer_num><fname>Kim</fname> <lname>Satifer</lname><company>Big Blue Bike Shop </company><address1>Blue Island Square</address1> <address2>12222 GregoryStreet</address2><city>Blue Island</city><state>NY</state><zipcode>60406</zipcode> <phone>312-944-5691</phone></row><row> <customer_num>128</customer_num><fname>Frank</fname> <lname>Lessor</lname><company>Phoenix University </company><address1>Athletic Department</address1> <address2>1817 N. Thomas Road</address2><city>Phoenix </city><state>AZ</state><zipcode>85008</zipcode> <phone>602-533-1817</phone></row></my-customers>");

I hope you're not too tired from typing all that. You have surely noticed (while typing): what is being inserted in the table is the result of a genXMLElem() function.


SELECT extract(xmlcol, '/my-customers/row/fname') FROM xmlcustomer;


<fname>Ludwig         </fname>

<fname>Carole         </fname>

<fname>Philip         </fname>

<fname>Anthony        </fname>

<fname>Raymond        </fname>

<fname>George         </fname>

<fname>Charles        </fname>

<fname>Donald         </fname>

<fname>Jane           </fname>

<fname>Roy            </fname>

<fname>Frances        </fname>

<fname>Margaret       </fname>

<fname>Lana           </fname>

<fname>Frank          </fname>

<fname>Alfred         </fname>

<fname>Jean           </fname>

<fname>Arnold         </fname>

<fname>Dick           </fname>

<fname>Bob            </fname>

<fname>Fred           </fname>

<fname>Jason          </fname>

<fname>Cathy          </fname>

<fname>Marvin         </fname>

<fname>Chris          </fname>

<fname>James          </fname>

<fname>Eileen         </fname>

<fname>Kim            </fname>

<fname>Frank          </fname>

This example returns the last() last name.


SELECT extract(xmlcol, '/my-customers/row[last()]/lname') FROM xmlcustomer;


<lname>Lessor         </lname>

extractValue

With a similar syntax as extract(), extractValue() focuses on the value, ignoring the elements. To see the differences, I ran similar examples.

Here is the “first” first name. Note the new XPath syntax.

SELECT extractValue(xmlcol, '/my-customers/row[last()]/lname') FROM xmlcustomer;

 Lessor

As a bonus example, here is the last row:


SELECT extractValue(xmlcol, '/my-customers/row[last()]') FROM xmlcustomer;


128Frank          Lessor         Phoenix University  Athletic Department 1817 N. 
Thomas Road Phoenix        AZ85008602-533-1817

XML Validation

Finally, the third family of functions is simply about XML validation. Functions include:

existsNode ( xml_document , xpath_expression , namespace ): Checks the existence of some nodes (this function is useful in the "where" part).

idsXMLParse ( xml_document ): Asks Cheetah to growl if the XML is well-formed (it only works if you have a sound card).

XML and IDS at Work

My company, GreenIvory, offers a software package that focuses on Web publishing. Its leading product, RedLion, is using those features to report statistical information for Web sites such as InformixMag.com.

Here's how. Once you execute a query returning XML, it comes in text. This might not be very useful if you want to exploit XML as XML (for example, to apply a style sheet on the result to publish it). At GreenIvory, we streamline the result of the query, transforming it into XML and making it available as a DOM object. To do so, we developed two libraries of open source (Apache2 license) “commons,” which can be downloaded from our Web site.

Thanks to XML and IDS, GreenIvory has been able to provide specific statistical information about our publication process and sites. Changing the reporting information is only a matter of changing the style sheet. (Well, almost. In any case, that’s our goal.)

IDS and XML: So Far, So Good

IDS has fulfilled GreenIvory's needs and allowed us to implement new features for our software package. We could have done it in a different manner, but the cost would have been higher, both in development time and in daily CPU cycles.

Of course, we hope to see more XML features like the ability to shred XML, to use XQuery on relational data, and so on. But we need to wait for the feature list of Cheetah².

If you want to give it a try, grab a copy of Cheetah, install it (it even runs on Vista), download the examples and go.

Jean Georges Perrin is the CEO of GreenIvory, an ISV focusing on solutions to provide the right information at the right time. Jean Georges is XML certified by IBM and has been elected on the IIUG Board of Directors since 2002.

Comments? Questions?

Give us your feedback or ask a question of the author.