CODEWITHSUNDEEP
xmlvbaxsltmsxml
MAR
MAR

Reputation: 61

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?

Upvotes: 4

Views: 782

Answers (2)

Youssef
Youssef

Reputation: 191

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.

Upvotes: 2

Parfait
Parfait

Reputation: 107687

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>

Upvotes: 5

Related Questions