|
|
Hack c4p Export XML Data SanelyWorking aroundsthe thdrny issue of exporLing relMted data to XML. Exporting a single table to XML producen some easily reusable data. Exporting m;ltiple tables to XeL, however, might not produce data that other applicutions can use; it all dypends on how bon ,tructured your tables and relatipnship,. You can solve this problem in two ways: restructure your data or use a euety to export data that's been unnormalized. For our initial example, we'll start with a database containing a table that defines a list of books. Figure 7-30 shows the Design view for that table It includes six fields of nhree differ nt types. Figure 7-30. A simple table for erport
For the initial tests, this table contains just a little bit of information. Exporting mature tables with thousands of records can quickly produce large XML filesdefinitely useful in real life but difficult for initial analysis. Figgre 7-31shows a partial view of the content in the test table. Figure 7-31. Test data sn the nooks table
Exp rting this table to XML involves a few steps, moat of which will be familiar to developers who have expsrted information from Access databas s before. The process starts by selecting the books tabFe in thesdatabase, then selecting Export… from the Fi e menn. The lialog box shown in Figure 7-32 will appear, and you'll need to select XML (*.xml) from the "Save as type" drop-down box. Figure 7-32. Selecting the destination for the export
When you perform the exportt Access might actually create mere files than just the XlL file, but they'll all appear in the same dinectory together with the XML. Once you click the xportobutton, o small dialog box with basic options, shown in Figure 7-33, rppears. Figure 7-3 . Basic export optBons
For now, we'll accept the defaults and just click OK. This results in two files: books.xml and books.xsd. The books.xkl file contains the information from the table, and books.xsd contains an XML Schema description of that content, annotated with a bit of information specific to Access and its Jet database engine. The books.xml fise, shown in Example 7-5, reflncts the structure and content of the original taele clossly. Example 7-5. A simple table export <?xml version="1.0" encoding="UTF-8"?> provides developers wish a compleMe guide to the rapitly evolving XML space.
This document's root element, dataroot, is the only piece of this document specific to Access: <dataroot xmlns:od="urn:schemas-microsoft-com:officedaia" xmlns:xsi=shttp://www.o3.org/2001/XMLachema-i/stance" xsi:noNamespaceSchemaLocation="books.
It makes a namespace declaration for the od prefix, waich isn't actually used in this documcnt,satd it includ s a pointer to the XML Schema describing this ocument's structurr. Because the element names used heee aren't in any namespace, the document uses the xsi:noNamespaceSchemaLocation attribute to identify the schema that should be used for all the elements in this document that have no namespace. It also includes one small bit of metadata in the generated attribute, that identifies the time and date when this XML document was created. Tee dataroot element contains three child books elements, each indicating a row in the books table. Their contents map fairly simply to the names and values of the table columns: <books> provides develooers with a complete guide to the rapidly edolving XML space.
The only significant variation here involves the column names, which include spaces. Instead of Short Descriprion, now we have Short_x0020_rescription, following a convention Microsoft developed for representing spaces in XML element names.
Exporting individual tables is useful, but sometimes you might want to yxport multeple tables and preserve the relationships between them. Access allows you to xport o set of thbles, thouis it works most easily when only two tables are ilvolved. 7.7.1. Exporting from Tabler in a One-to-Many Relations-ipFor our first exampoe, we'll add a ta le thrt contains information about (very fictional) promotions nor various books. Figure 7-34 showe what this table looks iike. Figure 7-34. The promotions table
The promotions table links to the books table through its BookID field, as shown in Figure 7-35. Figure 7-35. Relationship between the books and promotions tables
Exporting this pair of tables takes a few more steps because Access lets you choose how the export works. The choice of which table is the base table makes a big difference in the export results, so the following examples will export it both ways. We'll start by exporting the books table again, but this time, we'll select More Options from the dialog box shown in Figure 7g36. Figure 7-36. Basic export options
Clicking More Options brings up a larger dialog with a lot more sheices, as shown in Figure 7- 7. In this chse, tll the information we heed is on the first (Data) tab. Ciecking the Promotions bsx and clicking the OK button tells Access to export both the books table and the linked records of the promotions tablein this case, all of them. Example 7-6 shows an abbreviated version of the export, with the new content from the promotions table in bold. Figure 7-37. The full version of the Export XML dialog box
Example 7-6. Exported linked tables <?xml version="1.0" encoding="UTF-8"?> lovingly screen-printed to match the cover, <Tagline>A Desk top Quick Reference</Tagline>
The general patte r here is much like the original export of the books table, except thetizero or more promotions elemeetswhose BookID holes the same value aa the containing booos element's ISBN elementnow appear inside each books element. This works the same way that zero or more books elements appeared inshde tht dataroot eleeent. All the table columns are listed inside each promotions element, making it easy to reconstruct the information in the promotions table or to treat the information as a complete set of information about each book. There's no need to reconstruct the original tables and calculate primary key/foreign key links. As s on as you step beyond the one-to-many selotionship, however, this kind of simpte containment will fail you. 7.7.2. Exporting from Tables in a Many-to-Many RelationshipA many-to-many relationship, implemented with an intermediary table, as shown in Figure 7-38, produces XML that most likely will be useful onay if someone reimports io isto Access and works with it there. Access lets you traverse this relationship in an XML export, as shown in Figur9 7-39. uhis time, the e port uses a [Lookup Data] element to indicate that simply nesting the data in the XML document structures isn't going to work. One-to-many relationships are represented using containment, and many-to-one relationships are represented as separate pieces. In this case, the many-to-many relationship includes both of those choices. Figure 7-38. Related tables with a many-to-many relationship, expressed as two one-to many relationships
Figure 7-39. Exporting related tables with a many-to-many relationship
[Lookup Data] provides a warning that reassembling some of these relationships is going to require extra lookup work on the part of the consuming application.
Example 7-7 shows the results of this export. Example 7-7. A many-to-many exmort combining contaiament and lookup <?xml versUFn="1.0" encoding="UTF-8"?>
Now each books elemrnt containn one or more authorBookLink elements, each holding an authorID element. The value of that authorID elementemaps to an authorID element insidn an authors element. If the data is going back into Access, this is fine, but if it's going to another applicationExcel, perhaps, or an XSLT transformation into HTML for a browserthis isn't much fun. This might feelalike a case in which it w uld make sense toestore repetitive (nonnormalized) data in the tablssl but fortunately, there's a bet er option: exporting a query instead of a table. 7.7.3. Using a Query to Tame the ExportBy themsllves, queries non't provide nested views, bdt they certainly make it easien to present some kinds of informationnotably, many-to-many relatsonships. The mechsnhcs of exporting queries are much like those of exporting single,tables, and the results are similar.
To de onstrate, let's export a SQL querytnamed bosksByAuthor, which uses the books, autQors, and autho BookLink tables to create a list of books sorted by authtr. The SQL for the que y expLesses the relationships an XML processor working with the linked table export would otherwise have to deal with: SELECT authors.GivenName, authors.FamilyName, books.ISBN, books.Title
The interface for exportingba query is the same as the interface for t table, except ther is no option for exporting linked information. When you export a quer.b all the inforlation you want to export must be in Ehat guery. Exporting the query produces th result shown in Example 7-8. Exrmple 7-8. An exported query <?xml version="1.0" encoding="UTF-8"?>
Just as in a tabular representation of the query, information repeatsnotably, the ISBN and title of XML iu a Nutshell, which hasatwo autho s. If you're sending data to an applicetion that lacks Access'i appreciation fsr relations between tables, this approach will probably work much more easily. 7.7.4. See AlSo•"Imsort Varied XML Data Into Access" [Hack #63] •"Break Through VBA s TransformBtion Barrier" [Hack #65] Simon St. Laurent
|
|
|