- XML: Like a Database
- The Crafty Database
- Exporting Information from an Access Database to a File
- Importing XML Information into an Access Database
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Exporting Information from an Access Database to a File
It's remarkably simple to export the information from an Access table to an XML file. With the table selected (see Figure 3), simply choose File, Export from the menu. Figure 4 shows the resulting dialog box. Specify the target location, select XML in the Save As Type drop-down list, and enter a filename (defaults to the name of the table you're exporting).
)
Figure
3 For this example, we want to export the Products table.
)
Figure
4 Exporting the Products table as XML.
Figure 5 shows the next dialog box. You can choose to export just the data from the table (the resulting file will get an XML extension), the schema of the data (creating an XSD file) and/or the presentation of the data (saved in an XSL file). Access assumes that you want both the schema and the data but not the presentation.
)
Figure
5 Options for exporting.
If you click the More Options button, you get the dialog box shown in Figure 6. On the Data tab, you can specify whether you want to export all records in the table, or just the first record. The encoding method for the data defaults to UTF-8, or you can choose UTF-16.
)
Figure
6 Data export details.
Figure 7 shows the Schema tab. Not much detail is associated with the export of the schema, and your choices are limited to the export of the referential integrity information, the primary key-foreign key information, and the desired location for the resulting information—embedded within the XML document or stored as a separate document.
)
Figure
7 Schema export details.
Figure 8 shows the Presentation tab options. You can create an HTML document with which to view the associated information in the XML document (Figure 9 shows the result from the Crafty database), or create an Active Server Pages (ASP) file. The export location of the XSL file will also be the location of the resulting HTML file.
NOTE
If you're exporting an Access report as XML, export the graphics information with the options in the Include Report Images section of the dialog box (inactive in Figure 8).
)
Figure
8 Presentation export details.
)
Figure
9 HTML presentation of XML information. Because this presentation isn't
a dynamic page, of course, the numbers are unreliable.
Figure 10 shows the XML version generated as a result of the export.
)
Figure
10 The XML file generated from the Access export.
TIP
The XML file generated from an Access database can be extended to include more than one table and to take into account the relationships between tables that are exported.
Here's the actual XML:
<?xml version="1.0" encoding="UTF-8" ?> - <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2004-02-28T05:24:07"> - <Products> <ProductID>1</ProductID> <ProductName>seaside cottage</ProductName> <ProductDescription>painting</ProductDescription> <Catagory>artwork</Catagory> <UnitsInStock>3</UnitsInStock> <UnitPrice>75</UnitPrice> </Products> - <Products> <ProductID>2</ProductID> <ProductName>victorian bonnets</ProductName> <ProductDescription>cotton vicrotian style bonnets</ProductDescription> <Catagory>baby clothes</Catagory> <UnitsInStock>15</UnitsInStock> <UnitPrice>20</UnitPrice> </Products> - <Products> <ProductID>3</ProductID> <ProductName>custom rattle</ProductName> <ProductDescription>stuffed embroyered rattles</ProductDescription> <Catagory>baby items</Catagory> <UnitsInStock>32</UnitsInStock> <UnitPrice>9</UnitPrice> </Products> - <Products> <ProductID>4</ProductID> <ProductName>snowflake stars</ProductName> <ProductDescription>stars with snowflake pattern</ProductDescription> <Catagory>christmas</Catagory> <UnitsInStock>87</UnitsInStock> <UnitPrice>2</UnitPrice> </Products> - <Products> <ProductID>5</ProductID> <ProductName>flower pot candles</ProductName> <ProductDescription>candles in a flowerpot 3 inch</ProductDescription> <Catagory>candles</Catagory> <UnitsInStock>54</UnitsInStock> <UnitPrice>9.5</UnitPrice> </Products> - <Products> <ProductID>14</ProductID> <ProductName>flower pot candle</ProductName> <ProductDescription>candle in a flowerpot mini</ProductDescription> <Catagory>candles</Catagory> <UnitsInStock>46</UnitsInStock> <UnitPrice>2.75</UnitPrice> </Products> - <Products> <ProductID>15</ProductID> <ProductName>flower pot candle</ProductName> <ProductDescription>patio sized candle in flower pot</ProductDescription> <Catagory>candles</Catagory> <UnitsInStock>2</UnitsInStock> <UnitPrice>35</UnitPrice> </Products> - <Products> <ProductID>16</ProductID> <ProductName>custom bulbs</ProductName> <ProductDescription>hand painted customized glass bulbs</ProductDescription> <Catagory>christmas</Catagory> <UnitsInStock>75</UnitsInStock> <UnitPrice>12</UnitPrice> </Products> </dataroot>
And here's the presentation code that produces a more attractive result:
<HTML xmlns:signature="urn:schemas-microsoft-com:office:access">
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/>
</HEAD>
<BODY ONLOAD="ApplyTransform()">
</BODY>
<SCRIPT LANGUAGE="VBScript">
Option Explicit
Function ApplyTransform()
Dim objData, objStyle
Set objData = CreateDOM
LoadDOM objData, "Products.xml"
Set objStyle = CreateDOM
LoadDOM objStyle, "Products.xsl"
Document.Open "text/html","replace"
Document.Write objData.TransformNode(objStyle)
End Function
Function CreateDOM()
On Error Resume Next
Dim tmpDOM
Set tmpDOM = Nothing
Set tmpDOM = CreateObject("MSXML2.DOMDocument.5.0")
If tmpDOM Is Nothing Then
Set tmpDOM = CreateObject("MSXML2.DOMDocument.4.0")
End If
If tmpDOM Is Nothing Then
Set tmpDOM = CreateObject("MSXML.DOMDocument")
End If
Set CreateDOM = tmpDOM
End Function
Function LoadDOM(objDOM, strXMLFile)
objDOM.Async = False
objDOM.Load strXMLFile
If (objDOM.ParseError.ErrorCode <> 0) Then
MsgBox objDOM.ParseError.Reason
End If
End Function
</SCRIPT>
</HTML>
Now it's just a matter of uploading the information and files to the hosted web site, and arranging access to the information by buyers or end users.