Representation of Complex Structures

XML is capable of representing a hierarchy in a single file, and the resulting representation of the data will occupy even less space compared with what it would require in FoxPro if we tried to use a single table using a JOIN because only one of the pair of matching keys has to be sent. Even if it weren't smaller in terms of space, it's more methodologically elegant.

Let's build a hierarchical XML structure from an invoice header and the corresponding detail file. Assume the following header file:

 INVOICES.DBF: InvNum     Integer(4) ClientID   Integer(4) Date       Date   (4) Total      Numeric(8,2) 

and the following detail file:

 INVDETL.DBF: InvNum     Integer(4) LineNum    Integer(4) Quantity   Integer(4) ProductID  Char(20) UnitPrice  Numeric(8,2) Extended   Numeric(8,2) 

Normally, in order to send these records as XML, we'd need to use a JOIN :

 SELECT * FROM Invoices, INVDETLS  WHERE Invoices.INVNUM = INVDETLS.INVNUM ORDER BY Linea 

The resulting cursor would duplicate the InvNum field in the two tables, which is really not necessary because by definition they have the same value in each joined record. But even more, each record in the resulting cursor would have all of the INVOICE fields as well as all of the INVDETL fields.

If we simply join the two tables, the resulting XML is replete with redundancy, as shown in Listing 7.5.

Listing 7.5. XML Representation of Joined Tables

 <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <invoices>     <invoice>         <invnum_a>141</invnum_a>         <clientid>41</clientid>         <date>2004-04-01</date>         <total>192.00</total>         <invnum_b>141</invnum_b>         <linenum>1</linenum>         <quantity>3</quantity>         <productid>303142-A</productid>         <unitprice>12.95</unitprice>         <extended>38.85</extended>     </invoice>     <invoice>         <invnum_a>141</invnum_a>         <clientid>41</clientid>         <date>2004-04-01</date>         <total>192.00</total>         <invnum_b>141</invnum_b>         <linenum>2</linenum>         <quantity>4</quantity>         <productid>1041202</productid>         <unitprice>3.15</unitprice>         <extended>12.60</extended>     </invoice>     <invoice>         <invnum_a>142</invnum_a>         <clientid>43</clientid>         <date>2004-04-03</date>         <total>225.00</total>         <invnum_b>142</invnum_b>         <linenum>1</linenum>         <quantity>1</quantity>         <productid>2022201</productid>         <unitprice>25.00</unitprice>         <extended>25.00</extended>     </invoice>     <invoice>         <invnum_a>142</invnum_a>         <clientid>43</clientid>         <date>2004-04-03</date>         <total>225.00</total>         <invnum_b>142</invnum_b>         <linenum>2</linenum>         <quantity>2</quantity>         <productid>2016615</productid>         <unitprice>89.95</unitprice>         <extended>179.90</extended>     </invoice> </invoices> 

And the fact that there is no longer an element named INVNUM is irritating . Of course, we can write a more specific SQL string to fix the INVNUM element name problem. But it's still a clumsy and redundant solution.

Just in case you doubt whether you've got something here that can describe a table, open Visual Studio, select File, Open, File from the menu, and open Chapter7code\Redundant.XML , the text file containing this text. In the lower-left corner you'll see a Data tab; click it, and you'll see the screen shown in Figure 7.2.

Figure 7.2. Data Table View of a Flat File.

Notice the caption above the table name; it says Data Tables , not Data Table . That's because a single XML file can contain multiple tables. You'll see how this works shortly.