How do I get new lines when outputting the text for MSXML2 XML document in VBA?

I'm generating an XML document in VBA using the MSXML2.DOMDocument and then sending the XML property of the obje开发者_JAVA技巧ct to a remote server (via POST).

The resulting string in MSXML2.DOMDocument.XML has no newlines, and so it is one big blob of XML. Is there a way to get the ouptput to put a new line after every XML element, making the file more human readable?

This isn't exactly essential, as the file received on the server is going to be immediately parsed and the information stored in an SQL database, but this would help development and testing.

---

You could just replace every instance of ">" with ">" & vbCrLf using the Replace function before writing the file to disk.

Or you could just save the XML to disk as is and open it with Firefox or IE which should do the syntax highlighting for you.

---

Don't know if you've found a suitable solution/workaround, but I was looking at this issue in msxml and someone suggested using MXXMLWriter. You can pipe the output through a SAX parser (SAXXMLReader) and hook MXXMLWriter to it, as described here. It's somewhat ridiculous to have to re-parse the xml to get it formatted when you know the DOMDocument has all the information it needs to indent, but there you have it.

Actually I didn't have time to explore yet but apparently you can use MXXMLWriter unattached to a SAX reader to build the XML - instead of using DOMDocment - and get it to indent as you go instead. See instructions here.

---

Just wrote a VBA sub that indents XML document before saving it. Indentation is similar to what I see when formatting it in Visual Studio. Maybe it should be extended to handle not only subelements and CDatas.

Sub IndentXml(xml As IXMLDOMElement, Optional depth As Integer)
    If IsMissing(depth) Then
        depth = 0
    End If

    Dim txt As IXMLDOMText

    If Not (xml.OwnerDocument.DocumentElement Is xml) Then
        Set txt = xml.OwnerDocument.createTextNode(vbNewLine & String(depth, vbTab))
        xml.ParentNode.InsertBefore txt, xml
    End If

    Dim child As IXMLDOMNode
    Dim hasElements As Boolean
    hasElements = False
    For Each child In xml.ChildNodes
        If child.NodeType = NODE_ELEMENT Then
            IndentXml child, depth + 1
            hasElements = True
        ElseIf child.NodeType = NODE_CDATA_SECTION Then
            Set txt = xml.OwnerDocument.createTextNode(vbNewLine & String(depth + 1, vbTab))
            xml.InsertBefore txt, child
            hasElements = True
        End If
    Next

    If hasElements Then
        Set txt = xml.OwnerDocument.createTextNode(vbNewLine & String(depth, vbTab))
        xml.appendChild txt
    End If
End Sub

---

Maybe you should look at the freeware ChilKat Xml instead of the horrid MSXML and make your life easy. I remember struggling with indent and formatting with MSXML, and I know how frustrating it is.

Good luck.

---

Thanks Darth Jurassic for your code. Very handy!

In my own code the second "If" block tripped up on "xml.ParentNode" being Nothing. For my own purposes I changed the following line from:

If Not (xml.OwnerDocument.DocumentElement Is xml) Then

to:

If depth > 0 Then

This worked fine for what I need so thought I'd share it.