XML and Hierarchical Data

One of the most interesting developments in database handling is the capability to create hierarchical recordsets, where a record can actually contain an entire new recordset. XML documents represent a perfect way of storing hierarchical recordsets because you can enclose one set of elements inside another easily.

Here's an example; in this case, I'm adding records about deliveries made to the customers in customer.xml in a new XML document, deliveries.xml:

<?xml version="1.0"?>
<CUSTOMERS>
    <CUSTOMER>
        <NAME>Charles</NAME>
        <RECORD>
            <CUSTOMER_ID>58704</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Ham</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$1.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$1.49</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>
    <CUSTOMER>
        <NAME>Franklin</NAME>
        <RECORD>
            <CUSTOMER_ID>58705</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Produce</DEPARTMENT>
            <PRODUCT_NAME>Tomatoes</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$3.00</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$2.95</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>
    <CUSTOMER>
        <NAME>Phoebe</NAME>
        <RECORD>
            <CUSTOMER_ID>58706</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Turkey</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$4.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$8.99</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>
    <CUSTOMER>
        <NAME>Mark</NAME>
        <RECORD>
            <CUSTOMER_ID>58707</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Beef</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$3.95</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$6.95</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>
    <CUSTOMER>
        <NAME>Nancy</NAME>
        <RECORD>
            <CUSTOMER_ID>58708</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Frozen</DEPARTMENT>
            <PRODUCT_NAME>Broccoli</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$1.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$2.99</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>
</CUSTOMERS>

In this case, each <RECORD> element itself contains two <DELIVERY> elements (which contain <DATE> and <TOTAL_COST> elements). A DSO can't simply treat multiple enclosed records like this as a single record because that would give two or more fields in the record the same name. Instead, Internet Explorer makes the recordset into a hierarchical recordset and gives each <DELIVERY> element its own subrecordset.

How do you refer to a subrecordset in a hierarchical database? For example, how can you refer to the <DELIVERY> elements in each <RECORD> element? You do that by referring to a new recordset, RECORD.DELIVERY. This expression refers to the child recordset made up of the <DELIVERY> elements in the current record.

As usual, this is made easier to understand with an example, so take a look at this code. Here, I'm binding deliveries.xml to a table and displaying the <DELIVERY> records for each customer using tables. I start by binding a table to an XML data island and displaying the name of each customer, like this:

<HTML>
    <HEAD>
        <TITLE>
            Using XML With Hierarchical Records
        </TITLE>
    </HEAD>

    <BODY>

        <CENTER>
            <H1>
                Using XML With Hierarchical Records
            </H1>

            <XML SRC="deliveries.xml" ID=dsoCustomer></XML>

            <TABLE DATASRC="#dsoCustomer" BORDER="1">
                <TR>
                    <TH><DIV DATAFLD="NAME"></DIV></TH>
                    <TD>
            .
            .
            .

Next, I bind a table to the RECORD field in the current record:

<HTML>
    <HEAD>
        <TITLE>
            Using XML With Hierarchical Records
        </TITLE>
    </HEAD>

    <BODY>

        <CENTER>
            <H1>
                Using XML With Hierarchical Records
            </H1>

            <XML SRC="deliveries.xml" ID=dsoCustomer></XML>

            <TABLE DATASRC="#dsoCustomer" BORDER="1">
                <TR>
                    <TH><DIV DATAFLD="NAME"></DIV></TH>
                    <TD>
                        <TABLE DATASRC="#dsoCustomer"
                        DATAFLD="RECORD">
            .
            .
            .

To display the data from the <DATE> and <TOTAL_COST> elements in each <DELIVERY> record, I bind one final internal table to the RECORD.DELIVERY recordset:

<HTML>
    <HEAD>
        <TITLE>
            Using XML With Hierarchical Records
        </TITLE>
    </HEAD>

    <BODY>

        <CENTER>
            <H1>
                Using XML With Hierarchical Records
            </H1>

            <XML SRC="deliveries.xml" ID=dsoCustomer></XML>

            <TABLE DATASRC="#dsoCustomer" BORDER="1">
                <TR>
                    <TH><DIV DATAFLD="NAME"></DIV></TH>
                    <TD>
                        <TABLE DATASRC="#dsoCustomer"
                        DATAFLD="RECORD">
                            <TR>
                                <TD>
                                <TABLE DATASRC="#dsoCustomer"
                                    CELLPADDING = "5"
                                    DATAFLD="RECORD.DELIVERY">
                                    <TR ALIGN = "LEFT">
                                        <TH>Date</TH>
                                        <TH>Total Cost</TH>
                                    </TR>
                                    <TR ALIGN = "LEFT">
                                        <TD><DIV DATAFLD="DATE">
                                        </DIV></TD>
                                        <TD><DIV
                                        DATAFLD="TOTAL_COST">
                                        </DIV></TD>
                                    </TR>
                                </TABLE>
                                </TD>
                            </TR>
                        </TABLE>
                    </TD>
                </TR>
            </TABLE>
        </CENTER>
    </BODY>
</HTML>

This page appears in Internet Explorer in Figure 8.7. As you can see there, each customer's name is displayed next to the dates and costs of their deliveries. Now you're handling hierarchical recordsets and XML.

Figure 8.7. Displaying hierarchical recordsets in Internet Explorer.


Handling Variable-Size Hierarchical Data in XML Documents

We've seen that Internet Explorer DSOs can handle hierarchical recordsets when each record itself has an internal recordset. The internal recordsets that I used each contained two records, but that's hardly a realistic example; in real-world documents, recordsets can be of any length. How do the DSOs in Internet Explorer stack up here? Take a look at this new document, variable.xml, in which each internal recordset has between one and three <DELIVERY> records:

<?xml version="1.0"?>
<CUSTOMERS>

    <CUSTOMER>
        <NAME>Charles</NAME>
        <RECORD>
            <CUSTOMER_ID>58704</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Ham</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$1.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$1.49</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$1.49</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>

    <CUSTOMER>
        <NAME>Franklin</NAME>
        <RECORD>
            <CUSTOMER_ID>58705</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Produce</DEPARTMENT>
            <PRODUCT_NAME>Tomatoes</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$3.00</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>

    <CUSTOMER>
        <NAME>Phoebe</NAME>
        <RECORD>
            <CUSTOMER_ID>58706</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Turkey</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$4.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$8.99</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>

    <CUSTOMER>
        <NAME>Mark</NAME>
        <RECORD>
            <CUSTOMER_ID>58707</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Meat</DEPARTMENT>
            <PRODUCT_NAME>Beef</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$3.95</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$6.95</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>

    <CUSTOMER>
        <NAME>Nancy</NAME>
        <RECORD>
            <CUSTOMER_ID>58708</CUSTOMER_ID>
            <PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
            <DEPARTMENT>Frozen</DEPARTMENT>
            <PRODUCT_NAME>Broccoli</PRODUCT_NAME>
            <DELIVERY>
                <DATE>10/20/2001</DATE>
                <TOTAL_COST>$1.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>10/25/2001</DATE>
                <TOTAL_COST>$2.99</TOTAL_COST>
            </DELIVERY>
            <DELIVERY>
                <DATE>5-3-2002</DATE>
                <TOTAL_COST>$7200.00</TOTAL_COST>
            </DELIVERY>
        </RECORD>
    </CUSTOMER>

</CUSTOMERS>

In fact, this is not a problem; here's the page I'll use to display this data:

<HTML>
    <HEAD>
        <TITLE>
            Variable Size Hierarchical Records
        </TITLE>
    </HEAD>

    <BODY>

        <CENTER>
            <H1>
                Variable Size Hierarchical Records
            </H1>

            <XML SRC="variable.xml" ID="customers"></XML>

            <TABLE DATASRC="#customers" BORDER="1">
                <TR>
                    <TH><DIV DATAFLD="NAME"></DIV></TH>
                    <TD>
                        <TABLE DATASRC="#customers"
                        DATAFLD="RECORD">
                            <TR>
                                <TD>
                                <TABLE DATASRC="#customers"
                                    CELLPADDING = "3"
                                    DATAFLD="RECORD.DELIVERY">
                                    <TR ALIGN = "LEFT">
                                        <TH>Date</TH>
                                        <TH>Amount</TH>
                                    </TR>
                                    <TR ALIGN = "LEFT">
                                        <TD><DIV DATAFLD="DATE">
                                        </DIV></TD>
                                        <TD><DIV
                                        DATAFLD="TOTAL_COST">
                                        </DIV></TD>
                                    </TR>
                                </TABLE>
                                </TD>
                            </TR>
                        </TABLE>
                    </TD>
                </TR>
            </TABLE>
        </CENTER>
    </BODY>
</HTML>

You can see the results in Figure 8.8, where each <DELIVERY> recordset is correctly displayed, even though they have different number of records.

Figure 8.8. Variable-size hierarchical records in Internet Explorer.


You can also create the same page using the XML DSO applet instead of data islands:

<HTML>
    <HEAD>
        <TITLE>
            Variable Size Hierarchical Records
        </TITLE>
    </HEAD>

    <BODY>
        <CENTER>
            <H1>
                Variable Size Hierarchical Records
            </H1>

            <APPLET CODE="com.ms.xml.dso.XMLDSO.class"
                ID="customers"
                WIDTH="0" HEIGHT="0"
                MAYSCRIPT="true">
                <PARAM NAME="URL" VALUE="variable.xml">
            </APPLET>

            <TABLE DATASRC="#customers" BORDER="1">
                <TR>
                    <TH><DIV DATAFLD="NAME"></DIV></TH>
                    <TD>
                        <TABLE DATASRC="#customers"
                        DATAFLD="RECORD">
                            <TR ALIGN = CENTER>
                                <TD>Sales</TD>
                            </TR>
                            <TR>
                            .
                            .
                            .
                            </TR>
                        </TABLE>
                    </TD>
                </TR>
            </TABLE>
        </CENTER>
    </BODY>
</HTML>

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.216.149.94