What is the optimum way to create XML using MSXML

Question

At the moment I am creating XML like this - and it works great...

Private Function CreateDom()
    Dim dom
    Set dom = New DOMDocument
    dom.async = False
    dom.validateOnParse = False
    dom.resolveExternals = False
    dom.preserveWhiteSpace = True
    Set CreateDom = dom
End Function

Public Function generateXML(sourceFileLocation)

'I make an instance of the dom

Set dom = CreateDom

'This is how I setup a root node

Set rootXML= dom.createElement("root")
    dom.appendChild rootXML

'This is how I set attributes
questestinterop.setAttribute "attributeName", "attributeValue"

'setup a child node
Set childOfRoot = dom.createElement("childOfRoot")
rootXML.appendChild childOfRoot 

'This is how I set the text of the element

childOfRoot.Text = "Text Value"
End Function

This is ok in my basic example above, but lets say I have a greater amount of XML to create - I end up with LOADS of appendchilds and a lot of objects - this seams inefficient and prone to error - but has the advantage that I can add an object to a previously created one, at any point.

With MSXML I don't have InnerXML available to me so the code is verbose. I am after a more effective way of creating XML using MSXML and VBA/VB - or a best practice for this kind of work - I can't help but feel there is a better way.

UPDATE I mentioned above there was no InnerXML - but there is a way to load an XML snippet into the DOM

Sub MergeXML()

   'Define
   Dim oXml As New MSXML2.DOMDocument
   Dim oXml2 As New MSXML2.DOMDocument

   'Assign
   oXml.loadXML ("<SomeRootElement><Input></Input></SomeRootElement>")
   oXml2.loadXML ("<Output><SomeElement></SomeElement></Output>")
   'or assign via file
   'oXml.Load("c:\Xml.xml")
   'oXml2.Load("c:\Xml2.xml")

   'Process
   oXml.FirstChild.appendChild oXml2.selectSingleNode("//Output")

   'Destroy
   oXml.Save ("c:\NewXml.xml")
   Set oXml2 = Nothing
   Set oXml = Nothing

End Sub

source: http://p2p.wrox.com/beginning-vb-6/28319-xml-using-msxml2-domdocument-object.html

Answer 1

XML is often the representation of an object stored in a file. .Net has many popular package available that make serialization and deserialization very easily, enabling you to generate xml from object and object from xml.

VBA lacks the possibility of using those nice package, but I've used a module that essentially does the same. http://www.kudinov.ru/?p=21

This allows you to focus on constructing your class and manipulate your data. The module will take care of the XML creation for you.

UPDATE:

First create your Parent class

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "ParentClassContainer"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Option Explicit
Public Persons() As ChildClassWithEveryXmlAttributes

Secondly create your child class

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "ChildClassWithEveryXmlAttributes"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Option Explicit
Public FirstName As String
Public LastName as String
Public Birdthday as date

Third, make sure to include the serialize module

Lastly, you can use your objects and serialize it at the end

Sub testSerialize()
    Dim myObject As New ParentClassContainer
    Redim myObject.Persons(20)
    myObject.Persons(0).FirstName = "John"
    myObject.Persons(0).LastName = "Doe"
    myObject.Persons(0).Birdthday = #2015-05-21#

    Serialize myObject, "C:\test.xml", False
End Sub

So we have created an xml file without ever playing with the createElement and appendChild functions from msxml. This is less prone to error because you play with objects.

XML Output result

<?xml version="1.0"?>
<Object class="ParentClassContainer">
    <PropertyGet name="Persons" type="VT_EMPTY">
        <Object class="ChildClassWithEveryXml">
            <PropertyGet name="FirstName" type="VT_BSTR">
                <![CDATA[John]]>
            </PropertyGet>
            <PropertyPut name="FirstName" type="VT_BSTR"/>
            <PropertyGet name="LastName" type="VT_BSTR">
                <![CDATA[Doe]]>
            </PropertyGet>
            <PropertyPut name="LastName" type="VT_BSTR"/>
            <PropertyGet name="Birdthday" type="VT_DATE">
                <![CDATA[2015-05-21]]>
            </PropertyGet>
            <PropertyPut name="Birdthday" type="VT_DATE"/>
        </Object>
    </PropertyGet>
    <PropertyPut name="Persons" type="VT_VARIANT"/>
    <PropertyPutRef name="Persons" type="VT_EMPTY"/>
</Object>

I created an excel file for this, I don't know how to upload it here or if it's possible...

Sample excel file with Vba as requested