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

Question

<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?

Answer 1

Consider an alternative XSLT 1.0 processor if the MSXML version renders the undesired closing tags. One freely available version that ships with most Windows machines is NET's XslCompiledTransform.

This class can be accessed and run via a Powershell script which in turn can be run at command line or by Excel with Shell command. This XSLT version will render self-closing tags.

Input (save as Input.xml)

<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>
</Root>

XSLT (save as style.xsl)

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:strip-space elements="*"/>
    <xsl:output method="xml" indent="yes" encoding="UTF-8"/>
    
    <xsl:template match="node()|@*">
       <xsl:copy>
          <xsl:apply-templates select="node()|@*"/>
       </xsl:copy>
    </xsl:template>
    
</xsl:stylesheet>

Powershell (save as run_xslt.ps1 file)

# Load the style sheet.
$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load("C:\Path\To\style.xsl");

# Execute the transform and output the results to a file.
$xslt.Transform("C:\Path\To\input.xml", "C:\Path\To\output.xml");

VBA (blue screen should pop up with any error or output message)

Sub RunXSLT()
    Dim RetVal As Variant
    
    RetVal = Shell("Powershell.exe -ExecutionPolicy Bypass -noexit -File " & _
                   """C:\Path\To\run_xslt.ps1""", 1)
End Sub

Output (Output.xml)

<?xml version="1.0" encoding="utf-8"?>
<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>