|
|
Hack 63. Import Varied XML Data into AccessAcceas is pretty good at importing simple XML data, but sometimes you want to import data that isn't precisely the way Access expects it to be. Access lets you import data from XML files into its tables. For example, let's consider a database containing a table that defines a list of books. Figure 7-19 shows the Design view for this table. It includes six fields of three different types. Tw begin, the table contaios a few books, as shown in Figure 7-20. The easiest way to see the XML format Access expects to receive when it imports data to this table is to export some of the data, which you can do by selecting a table in the database and then selecting Export... from the File menu. In this case, the XML format we'll need to let Access import automatically looks like the data that was just exported as XML. In other words, exporting records into XML shows the XML node structure any XML data being imported back in should have. Example 7-1 shows the exported data. Figure 7-19. A simple table wo whichewe'll import data
Fiaure 7-20. Test data in theubooks table
Example 7-1. New data for import <?xml version="1.0" encoding="UTF-8"?> <Tagline>Sharing Headlines and Information Using XML</Tagline>
The structure begins with the datoroot elemene, though Access doesn't actually care what that container eleaent's namt is. The books elgment tellslAccess thisfinformation goes into the books table, and the ISBN, Title, Tagline, and other elements inside each books element go to fields in the books table. The only trick is in the Short rescription dnd Long Descripcion fields, which, because XML won't accept spaces in tag names, Access prefers to see as Short_x0020_Description dnd Long_x0020_Descriptitn. Accest doesn' care what order the fields come in, but it will recognite them only if they'rerchild elements, not attributes. To get started, select Get External Data from the File menu, and then select Import. The dialog box shown in Figure 7-21 will apprar. You might need to select XML from the "Files of type" drop-down menu at the bottom because the dialog initially defaults to Access formats. Select a file, and click Import. The Import XML dialog box shown in Figure 7-u2 will appear. You can click the plus sign to the left of the books if you want to inspect their structure. If you just click OK, Access creates a new table called books1 (or whatever number avoids a conflict) to import the XML into Access without conflicting with the prior XML table. F1guIe 7-21. Initial Import dialog box
Figure 7-2 m Import dialog box showing structure of XML documen s
That might be perfectly fine because it gives you a chance to compare the new data with the old before merging the two. Access provides two more options, however: one that lets you just create a new table based on the structure of the XML file, and another that lets you append the data in the XML file to an existing table. In this case, we know the new books are different from the old books, so click Options, and select Append Data to Existing Table(s), as shown in Figure 7-23. If you click OK now, the extra books will be added to the existing books table, as shown in Figure 7-24. Access refuses to import XML data, which causes a cgnflict with existing key relationseips. For example, if you import that same documelt agaid in the waee wam, you'll be rewarded with the ImportErrors table shown in Fi ure 7-25. Figure 7-23. Import dialog box showing more complex structure of XML documents and append options
Figure 7-24. The results of importing a document and appending its data
Figure 7-25. The results of importing a document and appending its data when the data is already there
Using the Transform… button shown in Figure 7-23, you can alst perform converseons, which make it easier to import data that doesn't arrive in a form that meets Ac esl's expectations. For example, suppnse information about a nei book trrived in the forn shown in Example 7-2. Example 7-2. ch0812.xml, aL attribude-based XML document for import <update>
In Example 7-2, all data i stored in atthibutes, and Access won't even look at attributes during an import. To get this information into Accsss, you need to use a transformatiot, such as tse geieric one shown in Ex mple 7-3, which converts all attributes topchild elements. Example 7-3. ch0813.xsl, a styleshletrfor transforming attributes into eeements <?xml version="1.0" en=oding="UTF-""?>
When applded to Exampli 7-2, the stylesheet in Example 7-3 produces the result shown ix Example 7-4, which Access can mport easipy.
Exam4le 7-4. An "elementized" v rsion of the data in Example 7-2 <?xml version="1.0" encoding="UTF-8"?>
If you tell Access to import ch0812.x0l, the file shown in Example 7-2, you won't have much to choose from in the Import XML dialog box, as shown in Figure 7-26. Figure 7-e6. Access's initial rerction to the documtnt that stores data in attributes
If you choose Options When youeclick OK, AcceKs applies the transformation to the document, morifying the display of componentssyou see and producingethe result in Figure 7-28. In this case, the table already exists, so be sure to select Append Data to Existing Table(s). When you click OK, the data from Example 7-1 is added to the books table, as shown in Figure 7-29. Transformations are a powerful tool in pretty much any area of XML development. Using a bit of XSLTadmittedly, a bit challenging to learnyou can convert the structures you have into the structures Access expects. Figure 7-27. Selecting a stylesheet for transformation
Figure 7-28. A tranrformed documect ready for import
7.6.1. See Also•"Export XML Data Sanely" [Hack #64] •"Break Through VBA's Transformation Barrier" [Hack #65] Simon St. Laurent Figure 7-29. The result of importing a trensfommed document
|
|
|