This follows on from a previous blog Export XML Data from Microsoft Access – Tips and Tricks, about how to export XML data from Microsoft Access using the interactive tools in Access. The example used the Northwind database to export the Order information for one Customer, so that they could use this XML document for merging with Microsoft Word using Content Controls. This blog continues this example, but shows how to use VBA to achieve the same result, and gets to grip with ExportXML method and how to manage the AdditionalData parameter.

A recap of the important points are:

• The root of the XML document is <dataroot>
• You cannot use a namespace, though the ExportXML automatically creates a namespace declaration xmlns:od=”urn:schemas-microsoft-com:officedata” which is only used if you are working with the xsd schema
• The name of each node is the same as the Access Query it is based on.
• The name of each tag (element) is the same as the field in the table or query.
• Fieldnames like “Zip/Postal Code” are escaped, and will look like “<ZIP_x002F_Postal_x0020_Code>” which is quite ugly, so you might want to put them in a query and rename the fields in camelcase to something like ZIP_PostalCode
• Table or Query names should also avoid spaces and punctuation except for underlines
• Any fields with null values are ignored, so to output empty fields use a function like NZ([table “” not found /]
  .[Fieldname],””). This is important if you need to create XML sample data, especially for destination applications that cannot use a Schema (xsd).
• Using a function like NZ on a PrimaryKey or ForeignKey (used in a relationship) will create a run-time error. The Access error message is non-descript so the cause of this error can be hard to track down.
• If you are wanting to use Queries with nested XML node they need to have relationship created for them.
• Tables/queries must be related through a single field
• Queries must be based on real tables, so ‘SELECT “” as [Dummy]’ to create a one record dataset will not work
• The node of any additional unrelated table/query is create at the root context

In addition to the queries in the previous blog I will also add a query for the Order Items, qryXMLOrderItems, which will act as a holder for each item, qryXMLOrderItem. The query relationships will look like this.

You might get away with using just the tables as the source, but I do not like the field naming in the Northwind database, plus I need some extra control so I am only using queries. After you have created the queries and set the relationship, and tested using the Access interactive export to XML, it is time to do some coding.

The ExportXML Method:

[sourcecode gutter=”false” language=”vb”] application.ExportXML ObjectType, _
DataSource As String, _
[DataTarget As String], _
[SchemaTarget As String], _
[PresentationTarget As String], _
[ImageTarget As String], _
[Encoding As AcExportXMLEncoding = acUTF8], _
[OtherFlags As AcExportXMLOtherFlags], _
[WhereCondition As String], _
[AdditionalData])
[/sourcecode]

Name	Description
ObjectType	A AcExportXMLObjectType that represents the type of AccessObject object to export. For our purposes the only objecttypes we are interested in are the acExportTable, and acExportQuery constants.
DataSource	The name of the table or query we are going to export. You cannot use a SELECT statement here
DataTarget	The file name and path for the exported XML data. If this argument is omitted, data is not exported. There is no overwrite warning with this.
SchemaTarget	The file name and path for the exported schema information. If this argument is omitted, schema information is not exported to a separate XSD file. At times we may want to use this, if the destination application for our XML understand XSD Schemas
PresentationTarget	The file name and path for the exported presentation information (XSL) for transforming to a HTML document. If this argument is omitted, presentation information is not exported. We are not going to use this.
ImageTarget	The path for exported images. If this argument is omitted, images are not exported. This is more useful for exporting of graphic reports with an XSL PresentationTarget file so the HTML can be displayed with images. We will not be using this.
Encoding	A AcExportXMLEncoding constant that specifies the text encoding to use for the exported XML. The default value is acUTF8, and that will be just fine for us.
OtherFlags	A bit mask that specifies other behaviors associated with exporting to XML. Can be a combination of AcExportXMLOtherFlags constants.
WhereCondition	Specifies a subset of records to be exported. For us this is where we can choose the specific customer that we want to export.
AdditionalData	Specifies additional tables or queries to export. I will come back to this further in this blog

To export the qryXMLCustomer to an XML document for the customer with the ID=1 we would use

[sourcecode gutter=”False” language=”vb”] Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="qryXMLCustomer", _
DataTarget:="C:\Users\Brian\Documents\Customer.XML", _
WhereCondition:="[ID] = 1"

[/sourcecode]

The AdditionalData parameter is where all the clever work is done and also where the most confusion reigns.

The Microsoft help screen for the AdditionalData Object doesn’t explain this at all well. It shows the following example for adding tables using the AdditionalData object;

[sourcecode gutter=”True” language=”vb”] Dim objOrderInfo As AdditionalData

Set objOrderInfo = Application.CreateAdditionalData

‘ Add the Orders and Order Details tables to the data to be exported.
objOrderInfo.Add "Orders"
objOrderInfo.Add "Order Details"

‘ Export the contents of the Customers table. The Orders and Order
‘ Details tables will be included in the XML file.
Application.ExportXML ObjectType:=acExportTable, DataSource:="Customers", _
DataTarget:="Customer Orders.xml", _
AdditionalData:=objOrderInfo
[/sourcecode]

This does create an XML document with nodes for “Customers”, and nest the “Orders” node, but the “Order Details” are not nested, instead are at the same context level as Customers. Microsoft have consistently got this wrong in the help screens and documentation for version after version. The following document outline will show the problem.

Instead the code to export the Customer with the ID=1 should be something like this.

[sourcecode gutter=”False” language=”vb”] Dim oAdditionalData As AdditionalData
Set oAdditionalData = Application.CreateAdditionalData
With oAdditionalData
‘ This adds a container node, so that all the individual orders are kept inside one node
.Add "qryCustomerOrders"
.Item("qryCustomerOrders").Add "qryXMLOrder"
‘ This is another container node
.Item("qryCustomerOrders").Item("qryXMLOrder").Add "qryXMLOrderItems"
.Item("qryCustomerOrders").Item("qryXMLOrder").Item("qryXMLOrderItems").Add "qryXMLOrderItem"

Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="qryXMLCustomer", _
DataTarget:="C:\Users\Brian\Documents\Customer.XML", _
WhereCondition:="[ID] = 1", _
AdditionalData:=oAdditionalData
End With
[/sourcecode]

The AdditionalData also needs to be nested, reflective of the relationships and the structure of the XML document and not just add them willy nilly.

You can add other nodes of related data, by inserting the .Add line in the correct location.

If you add a data node that is not related to the parent node it will output the node at the current node context with all records. This can be especially useful if you need to include Lookup tables or other configuration information, at the root level.
For example, adding a couple of Queries at the end of the AdditionalData

[sourcecode gutter=”False” language=”vb”] With oAdditionalData
‘ This adds a container node, so that all the individual orders are kept inside one node
.Add "qryCustomerOrders"
.Item("qryCustomerOrders").Add "qryXMLOrder"
‘ This is another container node
.Item("qryCustomerOrders").Item("qryXMLOrder").Add "qryXMLOrderItems"
.Item("qryCustomerOrders").Item("qryXMLOrder").Item("qryXMLOrderItems").Add "qryXMLOrderItem"
.Add "qryShippers" ‘ a query producing one record
.Add "qryShipper"
[/sourcecode]

would create a node at the same level as the qryXMLCustomer, called qryShippers, and inside would be all the shipper records returned by the qryShipper.

I like including repeating nodes such as each customer order (qryXMLOrder) inside a container node, (qryCustomerOrders). This is done by creating a dummy node, and relating them together in the case of qryXMLCustomer it was easy to create a one field query qryXMLCustomers with the ID, and establishing a relationship.

So how did I create the qryShippers container since there is not a ForeignKey that could be used with the Shippers table. I did this by creating a query like this:

[sourcecode gutter=”False” language=”sql”] SELECT ID AS Dummy FROM Shippers WHERE ID=1;
[/sourcecode]

and in the qryShipper

[sourcecode gutter=”False” language=”sql”] SELECT ID, Company, City, 1 AS Dummy FROM Shippers;
[/sourcecode]

and then setting a relationship using the dummy field.

I had to base the qryShippers on a real table, trying to do this “SELECT 1 AS Dummy” which would have also given me a one record result, does not work.

This concludes the two part blog about generating XML from Access. You might like to check out the other related blogs in the links below.

See also
Export XML Data from Microsoft Access – Part 1 – Tricks and Traps
Export XML Data from Microsoft Access – Part 3 – Matching a schema
Document Generation using Word Content Controls and Microsoft Access