Search code examples
xmlvbaxsltmsxml

VBA with XSLT to pretty print XML with end tags in the same line

<Root ID="123"   Name="Board">
    <Element1 name="AAA"/>
    <Element2 name="BBB"/>
    <Element3 name="CCC"/>
    <Element4 name="DDD" Age="56" Address="78"/>
    <Nested_Elements>
      <Nested Number="125"/>
      <Nested Number="250"/>
      <Nested Number="500"/>
      <Nested Number="1000"/>
    </Nested_Elements>
</Root>

I wrote VBA code to export the XML and use the XSLT style to pretty print it by adding this

    Set xslDoc = New MSXML2.DOMDocument
xslDoc.LoadXML "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" _
        & "<xsl:stylesheet version=" & Chr(34) & "1.0" & Chr(34) _
        & "                xmlns:xsl=" & Chr(34) & "http://www.w3.org/1999/XSL/Transform" & Chr(34) & ">" _
        & "  <xsl:strip-space elements=" & Chr(34) & "*" & Chr(34) & " />" _
        & "  <xsl:output method=" & Chr(34) & "xml" & Chr(34) & " indent=" & Chr(34) & "yes" & Chr(34) & "" _
        & "            encoding=" & Chr(34) & "UTF-8" & Chr(34) & "/>" _
        & "  <xsl:template match=" & Chr(34) & "node() | @*" & Chr(34) & ">" _
        & "    <xsl:copy>" _
        & "       <xsl:apply-templates select=" & Chr(34) & "node() | @*" & Chr(34) & " />" _
        & "    </xsl:copy>" _
        & "  </xsl:template>" _
        & "</xsl:stylesheet>"
xslDoc.async = False
Set XmlNewDoc = New MSXML2.DOMDocument
XDoc.transformNodeToObject xslDoc, XmlNewDoc   'Line to fix indention
XmlNewDoc.Save XmlFile

The problem is this will add extra end tags like this

<Root ID="123"   Name="Board">
<Element1 name="AAA">
</Element1>
<Element2 name="BBB">
</Element2>
<Element3 name="CCC">
</Element3>
<Element4 name="DDD" Age="56" Address="78">
</Element4>
<Nested_Elements>
  <Nested Number="125">
  </Nested>
  <Nested Number="250">
  </Nested>
  <Nested Number="500">
  </Nested>
  <Nested Number="1000">
  </Nested>
</Nested_Elements>

I know it shouldn't be any diffrent from XML poit of view ,but I need the files to have the same structures as old files for configuration managment problems,

Any Idea how to change it to produce the same end tags?

Solution

  • After struggling with XSLT to remove namespaces here How to ignore an XML namespace, I managed to prettyPrint the output, but still got those annoying <tag></tag> for empty tags, so I still rely on my initial function I used, and here it is for anyone facing the same issue (you only need to add a reference to Microsoft XML, v6.0) :

    Public Function PrettyPrintXML(XML As String) As String
  Dim Reader As New SAXXMLReader60, Writer As New MXXMLWriter60
  Writer.indent = True: Writer.standalone = False
  Writer.omitXMLDeclaration = True: Writer.Encoding = "utf-8"

  Set Reader.contentHandler = Writer: Set Reader.dtdHandler = Writer: Set Reader.errorHandler = Writer

  Call Reader.putProperty("http://xml.org/sax/properties/declaration-handler", Writer)
  Call Reader.putProperty("http://xml.org/sax/properties/lexical-handler", Writer)
  Call Reader.parse(XML) 'A document must contain exactly one root element
  PrettyPrintXML = Writer.output
End Function

    This will take care of indentation as well as self-closing <tags />, unlike the other solution Create XML using DOM in VBA by @Parfait which does prettyPrint the xml, but doesn't solve the closing tags issue.