Visually Exporting and Importing XML
Exporting a Microsoft Access Object
<?xml version="1.0" encoding="utf-8"?> <Pupils> <Students> <FirstName>Benjamin</FirstName> <LastName>Carson</LastName> <DateOfBirth>04/10/1995</DateOfBirth> <Gender>Male</Gender> </Students> <Students> <FirstName>Gertrude</FirstName> <LastName>Simms</LastName> <DateOfBirth>8/22/1993</DateOfBirth> <Gender>Female</Gender> </Students> <Students> <FirstName>Tracey</FirstName> <LastName>Sandt</LastName> <DateOfBirth>12/24/1997</DateOfBirth> <Gender>Unknown</Gender> </Students> <Students> <FirstName>Chrissie</FirstName> <LastName>Burchs</LastName> <DateOfBirth>02/06/1993</DateOfBirth> <Gender>Female</Gender> </Students> </Pupils>
Besides the obvious root node (named Pupils), this document clearly specifies a leading element (named Students) for each section. That leading element would serve as the table name and its child nodes would serve as the columns names. In some cases, you will have a complex but valid XML document such as made of a weird organization of elements, child nodes changing from one parent element to another. As a matter of fact, consider the following example of a document made of some elements with attributes while other elements from the same levels do not have the same structure:
<?xml version="1.0" encoding="utf-8" ?> <VideoCollection Description="Personal Video Collection"> <Videos ISBN="0-7888-1623-3" Screenplay="Marty Kaplan" StoryBy="Marty Kaplan and Jonathan Reynold"> <Title>The Distinguished Gentleman</Title> <Director>Jonathan Lynn</Director> <CastMembers> <Actor>Eddie Murphy</Actor> <Actor>Lane Smith</Actor> <Actor>Sheryl Lee Ralph</Actor> <Actor>Joe Don Baker</Actor> <Actor>Victoria Rowell</Actor> </CastMembers> <Length>112 Minutes</Length> <Format>DVD</Format> <Rating>R</Rating> </Videos> <Videos ISBN="0-7907-3900-3"> <Title WrittenBy="Charlie Peter">Her Alibi</Title> <Director>Bruce Beresford</Director> <Length>94 Mins</Length> <Format>DVD</Format> <Rating>PG-13</Rating> <Actors> <Actor>Tom Selleck</Actor> <Actor>Paulina Porizkova</Actor> <Actor>Wllimam Daniel</Actor> <Actor>James Farentiona</Actor> </Actors> </Videos> <Videos ForeignFilm="True"> <Title>Chalte Chalte</Title> <Director>Aziz Mirza</Director> <Length>145 Mins</Length> <Format>DVD</Format> <Rating>N/R</Rating> </Videos> </VideoCollection>
Notice that one Videos element has a child node name CastMembers that has its own children. Another Videos element has a child node named Actors. If you import this type of file, the XML parser would try to create a table for each set of elements on the same level.
To visually import records from an XML file and create a table, on the Ribbon, click External Data. In the Import section, click the XML File button. This would open the Get External Data XML File dialog box. To specify the file that has the records, click the Browse button. Locate and select the file. Then click OK. An Import XML dialog box would show up, allowing you to set some options. Once you are ready, click OK. An acknowledgement dialog box, titled Get External Data XML File would display. Once you are ready, click Close.
Practical Learning: Importing XML Records to Microsoft Access
Exporting a Microsoft Access Object
Microsoft Access makes it possible and easy to visually convert the records of a table or query to XML. To visually export the records of a table or view and create an XML file, perform one of the following actions:
In both cases, an Export XML File dialog box would come up, showing the path where the file would be saved, including its name. Make the desired changes and click OK. Another dialog box would come up, asking you what to save. It also asks whether you want it to create a schema file (XSD) and a style sheet (XSL) file. Normally, all of the columns in the table or query would be included in the XML file that would be created. If you want to remove some columns, click More Options. This would open the Export XML dialog box that allows you to specify more features. To accept the options or after making your selections, click OK.
The obvious advantage of visually exporting a the records of a table or query to create an XML file is that the process is particularly easy. In fact, you dont need to know anything about XML. So the advantage is that Microsoft Access does all the work for you. As a disadvantage, you may not like the way Microsoft Access lays out the tags.
Practical Learning: Exporting Microsoft Access Objects to XML
The Application Class and XML
Importing an XML File
In its large support for various types of databases, Microsoft Access has a class named Application. This class provides the ability to import records from an XML file out to export records from database objects such as tables or queries. Obviously before dealing with XML, you should create such a file and make sure it is valid. Of course, the document should start with a root element. That root should have one or more child elements. Those child elements should have the same name and the same structure (same names and same child nodes).
To provide the ability to import records from an XML file, the Application class is equipped with a method named ImportXML. Its syntax is:
Public Sub Application.ImportXML(Byval DataSource As String, _ Optional ImportOptions As AcImportXMLOption = acStructureAndData)
This method takes one required and one optional argument. The DataSource argument is the name of the XML file that holds the records you want to import. If you provide only the file name, Microsoft Access would look in the same directory as the current database. If the file is in another directory, provide its complete path.
The optional ImportOptions argument allows you to specify whether the imported records should be placed in an existing table or a new table should be created for the records. The ImportOptions argument is based on the AcImportXMLOption enumeration. Its members are:
Practical Learning: Importing Records From XML
Exporting an XML File
To easily support XML, the Application class is equipped with a method named ExportXML. Its syntax is:
Public Sub Application.ExportXML(ByVal ObjectType As AcExportXMLObjectType, _ ByVal DataSource As String, _ ByVal DataTarget As String, _ Optional SchemaTarget As String, _ Optional PresentationTarget As String, _ Optional ImageTarget As String, _ Optional Encoding As AcExportXMLEncoding = acUTF8, _ Optional OtherFlags As AcExportXMLOtherFlags, _ Optional WhereCondition As String, _ Optional AdditionalData As Variant)
This method takes two required arguments. The first argument specifies the type of object that holds the records. This argument is of type AcExportXMLObjectType, which is an enumeration. Its members are as follows:
The DataSource argument is the name of the object that holds the records. This argument is passed as a string. If you just specify the name of the file, Microsoft Access would create the XML file in the same directory as the database. If you want, pass a complete path where you want the file created. Also provide the name of the file and the .xml extension.
The DataTarget argument is the name of the file that will be created. You must provide a name and the .xml extension. If you want the file to be created in a particular folder, provide the complete path. In most cases, the name of the file should be the same as the table or query that holds the original records. Here is an example of calling the Application. To easily support XML, the Application.ExportXML() with the required arguments:
Private Sub cmdCreateXMLFile_Click() Application.ExportXML acExportQuery, "PayrollSummary", "PayrollSummary.xml" End Sub
Because the Application class is available by default, you can omit it when calling the function.
The other arguments are optional. The SchemaTarget argument specifies the name of the schema file that you want Microsoft Access to create during this operation. If you decide to pass this argumet, make sure you give the intended name of the file and an extension such as .xsd. Here is an example:
Private Sub cmdExportCustomers_Click() ExportXML acExportTable, "Customers", "Customers.xml", "Customers.xsd" End Sub
The PresentationTarget argument specifies an extensible style sheet transformation file to create for the database object. If you pass this argument, specify a file name and an xsl extention.
Private Sub cmdCreateXMLFile_Click() Application.ExportXML acExportQuery, _ "SeasonalWorkers", _ "SeasonalWorkers.xml", _ "SeasonalWorkers.xsd", _ "SeasonalWorkers.xsl" End Sub
If the export operation contains some images that need to be exported, the ImageTarget argument specifies the folder where the images should be stored. By default, the XML document is created with the UTF-8 encoding. If you want a different encoding, pass the Encoding argument with desired value. This argument is passed as a member of the AcExportXMLEncoding enumeartion. The available members are acUTF8 (the default) and acUTF16. Here is an example:
Private Sub cmdCreateXMLFile_Click() Application.ExportXML acExportQuery, _ "occupanciesSummary", _ "occupanciesSummary4.xml", _ "occupanciesSummary4.xsd", _ "occupanciesSummary4.xsl", _ , _ AcExportXMLEncoding.acUTF16 End Sub
The OtherFlags argument is passed as a logical expression that specifies what other operations should be performed when exporting XML. This argument is passed as one or a combination of members of the AcExportXMLOtherFlags enumeration. The members of this enumeration are:
As its name implies, the WhereCondition argument allows you to include only some records in the resulting XML file. Here is an example:
Private Sub cmdCreateXMLFile_Click() Application.ExportXML AcExportXMLObjectType.acExportTable, _ "Customers", _ "Customers.xml", _ "Customers.xsd", _ "Customers.xsl", _ , _ AcExportXMLEncoding.acUTF16, _ AcExportXMLOtherFlags.acEmbedSchema, _ "LastName = 'Johnson'" End Sub
If the OtherFlags argument is not passed with the acLiveReportSource value, the last argument, AdditionalData, allows you to add other tables to export.
Practical Learning: Importing Records From XML