Hack 87. Provide Complete XML Control to Any Version of Access
Support for XML has been growing through successive Access releases, but complete XML control still isn't available. For instance, Access 2003 supports importing and exporting XML, but even so, the level of functionality is limited. For example, you can't import attributes (a type of XML data).
Referencing an external XML parser not only improves the XML support, but also provides the same level of support to any version of Access. This is possible because the parser is an independent piece of technology. As long as you can reference it, you can use it to its fullest.
8.17.1. Referencing the Parser
In the Access VB editor, use the Tools References menu to open the References dialog box, shown in Figure 8-28.
Scroll through the list, and find Microsoft XML. The reference will include a version number; any version will do. If you are curious how the parser versions differ, visit Microsoft's web site (http://www.microsoft.com).
Figure 8-28. Adding a reference to the XML parser
With the reference set, you can work with XML in many sophisticated ways.This hack isn't the place to learn how to use the parser (see the end of the hack for some resources). Instead, we'll preview using the parser to load XML data and insert it into an Access table. Along the way, we'll accomplish a couple of tricks: filtering XML data and loading attributes.
An XML file filled with employee information has been prepared, as shown in Figure 8-29.
8.17.2. The Code
In an Access code module, the following code has been entered. This code uses objects available from the parser reference:
Sub read_xml() On Error GoTo err_end Dim conn As New ADODB.Connection Set conn = CurrentProject.Connection Dim xmlobj As DOMDocument Dim xml_list As IXMLDOMNodeList Dim xml_node As IXMLDOMNode Set xmlobj = New DOMDocument xmlobj.async = False xmlobj.Load "C:\Employees.xml" Set xml_list = xmlobj.selectNodes _ ("Employees/Department/Employee") For Each xml_node In xml_list ssql = "Insert Into tblEmployees Values (" & _ xml_node.childNodes(0).Text & ", '" & _ xml_node.childNodes(1).Text & "', '" & _ xml_node.parentNode.Attributes(0).Text & "')" conn.Execute ssql Next MsgBox "done" err_end: MsgBox Err.Description End Sub
Figure 8-29. The Employees.xml file
The XML file is loaded into the xmlobj object variable:
Typical XML objects are nodes and node lists. A list is a collection of nodes. The actual nodes are the employee elements, which are children of the department nodes:
Set xml_list = xmlobj.selectNodes _ ("Employees/Department/Employee")
Employee nodes have two children: EmployeeID and Name. These child elements and the parent department element are the basis from which a SQL Insert statement is created.
ssql = "Insert Into tblEmployees Values (" & _ xml_node.childNodes(0).Text & ", '" & _ xml_node.childNodes(1).Text & "', '" & _ xml_node.parentNode.Attributes(0).Text & "')"
After the routine runs, the tblEmployees table is populated with the XML data, as shown in Figure 8-30.
Figure 8-30. The XML data now in Access
So, in just a short routine, we've accomplished two things that are typically taken for granted as being impossible. One is that now, only Access 2003 can work with XML in a robust way, and the other is that attributes can't be imported. The routine in this hack will work with any version of Access that references the parser and clearly has no problem putting an attribute's value into an Access table.
8.17.3. See Also