Create custom XML from Access VBA

As suggested, you want to use the MSXML file. Your problem is you (unfortunately) have name spaces, and 99% of basic (and clean) VBA examples don’t include the name space. In a simple nut shell, name spaces are the cause of world poverty, expensive software, and a general suffering in the software industry. You also don’t state if you have to create a separate xml file for each record, or you want the data in a single xml output file. The following code shows a neat-o way to deal with the name space. I also assume that you are putting one access record into one xml file, and then writing it out. If you have to append “many” records to one xml file, then my use of the xPath “//” will not work, and you have to create a new node and use “/” for your xPath referencing.

However there are MANY msxml examples, it usually just getting started. And as noted, unfortunately the vast majority of those examples leave out the HORRIFYING and MASSIVE and NASTY and PAINFUL issue of namespaces. However, the code will look something like this:

Sub xmlExport()

  Dim xml        As New MSXML2.DOMDocument60
  Dim xmldoc     As MSXML2.IXMLDOMNode

  Dim strTemplate   As String
  Dim strOutFile    As String

  strTemplate = "c:\test2\template.xml"

  If xml.Load(strTemplate) = False Then
     MsgBox "cannot open xml template"
  End If

  xml.SetProperty "SelectionLanguage", "XPath"
  xml.SetProperty "SelectionNamespaces", "xmlns:x='" & xml.namespaces(1) & "'"

  Dim rst        As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset("SELECT * from tblDelivery where City = 'Edmonton'")

  Dim i As Integer

  Do While rst.EOF = False

     i = i + 1      ' sequence number
     xml.SelectSingleNode("//x:SequenceNo").Text = i
     xml.SelectSingleNode("//x:RsNumber").Text = rst!ResNumber
     xml.SelectSingleNode("//x:LicensePlate").Text = rst!License
     xml.SelectSingleNode("//x:FirstName").Text = rst!FirstName
     ' . etc . etc.

     strOutFile = "c:\outdata\xml" & i & ".xml"

     xml.Save (strOutFile)

     rst.MoveNext

  Loop

  rst.close
End Sub

Now having said above if anyone has a nice solution for working with xPath and a namespace, then I will say "thank you very much", and re-edit this post to remove the rant about world poverty and the pain and suffering xPath becomes when name spaces are involved.

There are several approaches you could take, which is best is up for debate. That said I would choose to use the MSXML object library if VBA is a requirement, if it is not I would choose C# create the necessary classes and serialize.

1) Use the MSXML library, here is a good example of how to create a XML document using it. Creating XML with MSXML

2) Create classes and serialize using an external vba library available for download Serialize with vba library

3) Create using plain text - this could be messy and hard to maintain.

4) If you have a choice other than VBA, I would look at C# or VB.NET and create the required classes (or easily generate them from the XML) and then serialize.