Tornado

Display XML data in tree format. XML datatype is supported by most database like MSSQL, ORACLE and DB2. The XML data type comes in an XML format and can be stored in text, memo or XML fields. When XML data is stored in XML field format, it can be queried using Xquery.

Instead of storing the data as individual columns/fields, we can store them n a single column in an XML structure. The advantages and disadvantages of using XML data structure in a database is being discussed in numerous forums. In the eyes of a middleware developer, this feature is definitely a highlight.

Tornado uses the Java Applet ASPdbTree to display the XML tree. This GOLD version features has been extracted for all the versions. There are three files that are required to implement the XML tree -

c:\inetpub\wwwroot\tronado\jars\ASPdbtree.class (basic tree applet file)
c:\inetpub\wwwroot\tornado\images-net\aspdbmenuicons.gif (associated icons files for applet).
c:\inetpub\wwwroot\tornado\images-net\aspdbmenuscroll.gif (associated menu files for applet)

The gif files are located at the image-net directory but can be specified by the user using the tid option in the dbTreeParams property. For example -

        x.dbTreeParams = "height=400| tid=/e/myimagedir"

The following example code creates an XML field -

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

The following code insert data into the XML field -

INSERT INTO docs VALUES (1, '<book genre="security"
publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<first-name>Michael</first-name>
<last-name>Howard</last-name>
</author>
<author>
<first-name>David</first-name>
<last-name>LeBlanc</last-name>
</author>
<price>39.99</price>
</book>')

INSERT INTO docs VALUES (2, 
'<doc id="123">
<sections>
<section num="1"><title>XML Schema</title></section>
<section num="3"><title>Benefits</title></section>
<section num="4"><title>Features</title></section>
</sections>
</doc>')

The following code query the XML field -

SELECT pk, xCol.query('/doc[@id = 123]//section') FROM docs

db2-fn:sqlquery("
SELECT purchase_order FROM business.orders
WHERE ship_date = '2005-06-15' ")/shipping_address/city

XML in Access

ACCESS has no XML data type hence XQuery type query cannot be used to retrieve records. We can still store the XML data in text or memo fields. The display process is identical to the SQL class datasource. A demo setup will be to add two columns to the employees table of the Northwind database. The first column is XMLProfile and the Second column is XMLAddr. A sample record of these two fields are -

<XmlAddress id=''><Address>507 - 20th Ave. E.</Address><City>Seattle</City><Region>WA</Region><PostalCode>98122</PostalCode>
<Country>USA</Country></XmlAddress>

<XmlProfile id=''><Title>Sales Representative</Title><TitleOfCourtesy>Ms.</TitleOfCourtesy><BirthDate>08-Dec-48</BirthDate><HireDate>01-May-92</HireDate>
<Phone>(206) 555-9857</Phone><Extension>5467</Extension></XmlProfile>

The following code will display the two columns -

Dim x As New Tornado.z()
x.dbQP = "U=1| ps=5| s=space| D=/Tornado/DB/NWIND-XML.MDB"
x.dbSQL = "SELECT * FROM Employees"
x.dbGridDisplayFlds = "XMLProfile, XMLAddr"
x.dbTextHolder = "Title=My XML Report"
x.dbMode = "ty=XT"
x.dbMagicCell = "fi=XMLAddr| mac={id=''+id='#2# #1#'},fi=XMLProfile| mac={id=''+id='#0#'}"
x.dbTreeParams = "height=400"
x.ASPdbNET()

The following example adds a custom grid template to position the XML tree on the right side -

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
Dim x As New Tornado.z()
'... set id file (idd) in curdir
x.dbQP = "U=1| ps=-1| s=1| D=/tornado/db/EMPLOYEE-XML.MDB| Ni=none| gdf=1,2"
x.dbSQL = "SELECT * FROM Employees"
x.dbTextHolder = "Title=My XML Report| sub=Database (XML) within a database"
x.dbMemoFlds = "f=XMLAddr| xml=true| Title=Employee XML Profile"
x.dbMode = "type=Grid| Ind=1"
'... Make XML on the side instead of below.
x.dbGridTemplate = "<center><table class='nb'><tr valign='top'><td>[[Grid]]<p>[[GridNav]]<p>[[GridStat]]</td><td>[[Memo]]</td></th></table></center>"
'... modify XML data content in runtime using magiccell!
'... data macro = id='' so no blowup even if magiccell fails. Replace with id=''+id='#2# #1#'
x.dbMagicCell = "f=XMLAddr| mac={id=''+id='#2# #1#'}"
'...set tree icon file is in curdir (tid)
x.dbTreeParams = "height=400 "
x.ASPdbNET()
response.write("This example uses a modified Northwind Employees table with two text fields containing XML code to illustrate DB within a cell.")
End Sub
</script>

Note that instead of displaying 5 rows of 11 fields of data, only two 'trees' will be displayed. The XMLAddr field will carry 5 fields and the XMLProfile will carry 6 fields.

The Attribute field of the XML root is id=''. Normally the attributes of the XML root is hard coded to reflect the id of the tree. We are going to use the MagicCell to fill this attribute and make it dynamic. The MagicCell code uses a replace function to replace id='' with id='dynamic value' where dynamic value is the EmployeeID in the XMLAddr column and First and Last name for the CMLPorfile column. The (+) delimiter delimits the original and replace values. Refer to the MagicCell documentation.

x.dbMagicCell = "fi=XMLAddr| mac={id=''+id='#2# #1#'},fi=XMLProfile| mac={id=''+id='#0#'}"

A new dbMode value for the XML tree is 'XT' or 'XMLtree'. When this mode is specified, the XML template (dbXmlTreeTemplate) will be used. Default value of this template is -

<center>[[GridNav]]<p>[[GridStat]]<p>[[XMLTREE]]</center>

Special Topic - XML