What is the optimal way to generate XML using MSXML

Question

What is the optimal way to generate XML using MSXML

I am currently 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 fine in my basic example above, but lets say I have more XML data to create - I get LOADS from appendchilds and a lot of objects - these seams are inefficient and error prone - but have the advantage that I can add an object to the previously created one in any moment.

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

UPDATE I already mentioned that InnerXML was not there, but there is a way to load XML fragment into 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

+3

xml vba msxml

Rob May 18 '15 at 9:38

source to share

1 answer

GELR · Accepted Answer · 2015-05-21T15:10:36+0000

XML is often a representation of an object stored in a file..Net has many popular packages that simplify serialization and deserialization by allowing you to generate xml from an object and an object from xml.

VBA has no way of using this nice package, but I used a module that essentially does the same thing. http://www.kudinov.ru/?p=21

This allows you to focus on building your class and managing your data. The module will take care of generating the XML for you.

UPDATE:

First create the 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

Second, create a 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, be sure to include the serialization module

Finally, you can use your objects and serialize them 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 created the xml file without playing with the createElement and appendChild functions from msxml. It's less error prone because you are playing 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 download it or if it is possible ...

Example excel file with Vba on demand

What is the optimal way to generate XML using MSXML

More articles: