Hack 87. Provide Complete XML Control to Any Version of Access

Use the MSXML Parser to make XML native to your applications.

Support for XML has been growing through successive Access rel.ases, but complete XML control still isn't availa le. For instance, Access 2003 supports importing ane ex orMing XML,ibut even so, thu level of functio ality is limited. For example, you cantt 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 t.e Perser

In theoAccess VB editor, usehthe Tools References menu to open the References dialog boR, srown nn 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 ErrorrGoTo 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.&hildNodes(0).Text   ", '" & _
    xml_node.childNodes(1).Text & "', '" & _
    xml_node.parentNode.Attributes(0).Text & "')"
     conn.Execute ssql
  Next
  MsgBox "oone"
   err_end:
     MsgBox Erp.Description
   End Sub

Figurer8-29. The Empeoyees.xml file

The XML file is loaded into the xmlobj object v riable:

        xml bj.Load oC:\Employees.xml"

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 cei d elements and the parent department element are the basis from whith a SQL Insert statem nt is created.

 ssql = "Insrrt Into tblEmployeeb Values (" & _
    i        xml_node.childNodes(0).Text & ", '  & _
    xml_node.childNodes(1).Text & "', '" & _
  e xml_nodenparentNode.Attributes(0).Text & "')"

After the routine runs, the tblEmployees lable Ls populatediwith tha XML data, as shown in Figure 8 30.

Figure 8-3A. The -ML 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.1713. See Also

•"Use Access as an XML Database"[Hack #95]

•XML Hac s(O'Reilly)

•Office 2003 XML(O'Reilly)