XML

XML (eXtensible Markup Language) is a hot new buzzword that seems to be on everyone's lips at the moment. Ask some people any question you like, and they'll tell you XML is the answer! But what is it? And why are people so interested in it?

Actually it's a very simple idea, and simple ideas that are good tend to go a long way. XML is just way to describe data in a text-based format, and in a standard way. I like to think of an XML file as a super- powered CSV file. We saw that CSV files were a nice way to transfer data in and out of a database (or any system as a matter of fact), but that they lost crucial information during the process, such as the details of the data types or indexes. XML gives us the mechanism to keep all that data, and any other data safe and sound. But it doesn't lose the ability to be used in pretty much any system, and there in lies its strength. It has all the convenience of CSV files, because anything can use it, and it adds a strong way to describe the data in as much detail as we want.

We'll be exploring XML in more detail in Chapter 18, but for this section, we are going to see how easy it is to export our data to an XML format.

To export data to XML we use the ExportXML method:

 Application.ExportXML (ObjectType, DataSource, DataTarget, SchemaTarget, , PresentationTarget, ImageTarget, Encoding, OtherFlags) 

Argument	Desciption
ObjectType	The type of transfer to be performed. This must be one of: acExportDataAccessPage acExportForm acExportFunction acExportQuery acExportReport acExportServerView acExportStoredProcedure acExportTable
DataSource	The name of the Access object you want to export. If left blank it will use the currently open object of the type specified in the ObjectType argument.
DataTarget	The file name and path for the exported data. If you skip this, then data is not exported, which may be desirable if you just want to export to one of the other targets.
SchemaTarget	This is file name and path for the exported schema information. Schema information describes the structure of the data, such as the data types used, or if a particular field is the primary key. If this argument is omitted, schema information is embedded in the data document rather than as a separate file.
PresentationTarget	The file name and path for the exported presentation information. If this argument is omitted, presentation information is not exported. This file will contain XSL that is used to "transform" the XML to HTML, and as such would usually have an .xsl extension.
Encoding	The text encoding to use for the exported XML. Can be one of: acEUCJ acUCS2 acUCS4 acUTF16 acUTF8 If not specified acUTF8 will be used (8-bit Unicode) and will be fine for more cases.
OtherFlags	The OtherFlags argument allows you to specify among other things if the web page that performs the actual transformation should be HTM or ASP. It does this through a bit mask with the following values: 1 Related tables - Means that the export includes the "many" tables for the object specified by DataSource . 2 Relational properties - If you export related tables, this will also creates relational schema properties. 4 Run from server - Means the export pages will be created as ASP pages (Active Server pahges); otherwise , default is HTML. Only applies when exporting reports . 8 Special properties - Creates extended property schema properties.
	To use a bit mask for this argument, just add the values up. So if you want related tables, relational properties, and ASP you would add 1+2+4 giving you 7 as the value to set. We'll take a more detailed look at bitmasks later in the book.

If you want to import XML data into the database then you can use the ImportXML method:

Application.ImportXML (Datasource, ImportOptions) Argument	Desciption
DataSource	This is a string specifying the name and path of the XML file to import. Note that the file name of the XML file does NOT have to relate to the name of the table you are importing, rather it is the XML tags inside the file that determine the table that the data will be imported to. This is important when considering the next parameter ( ImportOptions ).
ImportOptions	One of the following instrinsic constants : acAppendData - this option means that if the table to be imported already exists then the imported data will be appended to it. If the table does not exist then the import will fail and Access will create an ImportErrors table detailing the failure. acStructureAndData - Access will create the table and import the data into it. If a table with the same name as the table to be imported already exists then Access will create a new table and append a number to the end of the name (for example Company1 ). For Access to be able to import the structure then there must be an embedded or linked schema file (XSD) for the XML file. We will see an example of this below. acStructureOnly - Access will create the table structure only but will not import the data into it. If a table with the same name as the table to be imported already exists then Access will create a new table and append a number to the end of the name (for example Company1 ) For Access to be able to import the structure then there must be an embedded or linked schema file (XSD) for the XML file. We will see an example of this below.

Try It Out-Exporting a Table to XML

1. Open up frmImportExport in design view.

2. Add another button, calling it cmdExportXML , and give it a caption of Export Company XML .

   In the Click event of the button, add the following code:

      Application.ExportXML acExportTable, "tblCompany", "c:\BegAccessVBA2002\Company.xml", _     "c:\BegAccessVBA2002\company.xsd", "c:\BegAccessVBA2002\company.xsl"     MsgBox "Company details exported to XML"   

3. Flip back to Access, switch the form into Form view, and click the Export Company XML button.

4. When the export has finished, take a look at the files it's created:

   Company.xml - this contains the XML data. Open it up in Internet Explorer or Notepad and you will see how the data is represented. This is not meant for human consumption, rather it is just a storage for the data.

   

   Company.xsl - this is the XSL code that was executed by Access to produce the HTML file. XSL stands for eXtensible Style Language and is a way of 'transforming' XML into something more human-friendly.

   

   Company.xsd - this is the schema for the data - it describes the structure of the data. If we were to import the data again from the XML file, having the schema available too means that we won't lose important information such as which field is the primary key. Open it up in notepad to see the description of the structure of the Company table:

   

   Company.htm - here is a HTML representation of our table - not that pretty perhaps, but in the next step we'll export a report with much nicer formatting:

   

Try It Out-Exporting a Report using XML to get a HTML Web Report

1. Open up frmImportExport in design view.

2. Add another button, calling it cmdExportXMLReport , and give it a caption of Export Suppliers XML Report .

3. In the Click event of the button, add the following code:

    Application.ExportXML acExportReport, "Suppliers", "c:\BegAccessVBA2002\Suppliers.xml", _    "c:\BegAccessVBA2002\Suppliers.xsd", "c:\BegAccessVBA2002\suppliers.xsl" MsgBox "Suppliers report exported to XML" 

4. Flip back to Access, switch the form into Form view, and click the Export Company XML button.

5. When the export has finished, take a look at the suppliers.htm file it's created: it is a pretty good representation of our Access report that we can now publish on the Web. One thing to bear in mind is that even if your original report spanned multiple pages, it will now be displayed on a single HTML page.

   

Try It Out-Importing XML Data

1. Open up frmImportExport in design view.

2. Add another button, calling it cmdImportXML , and give it a caption of Import Company XML .

3. In the Click event of the button, add the following code:

      Application.ImportXML "c:\BegAccessVBA2002\Company.xml", acStructureAndData     MsgBox "Company Details Imported from XML file"   

4. Flip back to Access, switch the form into Form view, and click the Export Company XML button, so that we have some XML data to import.

5. Now click the Import Company XML button.

If you now take a look at the tables in the database window, you will see that you have a new table, Company1, which contains the data you have just imported.