Looping XML into Single Excel Cell using XSLT

Question

Looking to transform (XSL, XSLT to Excel):

<NessusClientData_v2>
 <Report name="FAKEDB" xmlns:cm="http://www.nessus.org/cm">
  <ReportHost name="192.168.1.1">
   <HostProperties>
    <tag name="operating-system">Microsoft Windows Server 2008 R2 Enterprise Service Pack 1</tag>
    <tag name="host-fqdn">FAKEDB</tag>
   </HostProperties>
  <ReportItem port="0" svc_name="general" protocol="tcp" severity="0" pluginID="19506" pluginName="Nessus Scan Information" pluginFamily="Settings">
  </ReportItem>
  <ReportItem port="1122" svc_name="availant-mgr?" protocol="tcp" severity="0" pluginID="11219" pluginName="Nessus SYN scanner" pluginFamily="Port scanners">
  </ReportItem>
  <ReportItem port="1122" svc_name="ssh" protocol="tcp" severity="2" pluginID="10882" pluginName="SSH Protocol Version 1 Session Key Retrieval" pluginFamily="General">
  </ReportItem>
 </Report>
 <Report name="FAKEAPP" xmlns:cm="http://www.nessus.org/cm">
  <ReportHost name="192.168.1.2">
   <HostProperties>
    <tag name="operating-system">Microsoft Windows Server 2008 R2 Enterprise Service Pack 1</tag>
    <tag name="host-fqdn">FAKEDB</tag>
   </HostProperties>
  <ReportItem port="1122" svc_name="availant-mgr?" protocol="tcp" severity="0" pluginID="11219" pluginName="Nessus SYN scanner" pluginFamily="Port scanners">
  </ReportItem>
  <ReportItem port="1122" svc_name="ssh" protocol="tcp" severity="2" pluginID="10882" pluginName="SSH Protocol Version 1 Session Key Retrieval" pluginFamily="General">
  </ReportItem>
  <ReportItem port="47001" svc_name="www" protocol="tcp" severity="0" pluginID="11219" pluginName="Nessus SYN scanner" pluginFamily="Port scanners">
  </ReportItem>
  <ReportItem port="49152" svc_name="dce-rpc" protocol="tcp" severity="0" pluginID="11219" pluginName="Nessus SYN scanner" pluginFamily="Port scanners">
  </ReportItem>
  <ReportItem port="0" svc_name="general" protocol="udp" severity="0" pluginID="10287" pluginName="Traceroute Information" pluginFamily="General">
  </ReportItem>
  <ReportItem port="139" svc_name="smb" protocol="tcp" severity="0" pluginID="11011" pluginName="Microsoft Windows SMB Service Detection" pluginFamily="Windows">
  </ReportItem>
 </Report>

Into something like:

Port    Service         FQDN
====    ========        =====
1122    availant-mgr?   FAKEDB, FAKEAPP
1122    SSH             FAKEDB, FAKEAPP
47001   www             FAKEAPP

I can do straight conversions, but nothing like this where I add multiple node pieces into a single cell. I got a decent sample using a "generate-id" call, but I can't get it into separate columns nor can I get it to do the needed logic:

Only return when pluginID = 11219
Create a new line when the port & svc_name are not the same.

Anything someone can point me to would be great.

Thanks. Sean.

This is a grouping question - do a search for that. Note that answers are different for XSLT 1.0 or 2.0. — michael.hor257k
– michael.hor257k, Commented Apr 22, 2016 at 5:16

Parfait · Accepted Answer · 2016-04-22 04:27:35Z

1

Consider using the Muenchian Method which can use a defined key to group ReportItem nodes by @port and svc_name.

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"
               xmlns:cm="http://www.nessus.org/cm">
<xsl:output version="1.0" encoding="UTF-8" indent="yes" />
<xsl:strip-space elements="*"/>

<xsl:key name="portsrvkey" match="ReportItem" use="concat(@port,@svc_name)" />

  <xsl:template match="/">
    <root>
      <xsl:apply-templates select="*"/>
    </root>
  </xsl:template>

  <xsl:template match="ReportHost">    
    <xsl:apply-templates select="ReportItem[generate-id()= 
                         generate-id(key('portsrvkey', concat(@port,@svc_name))[1])]"/>    
  </xsl:template>

  <xsl:template match="ReportItem[generate-id()= 
                       generate-id(key('portsrvkey', concat(@port,@svc_name))[1])]">
    <xsl:variable name="key" select="key('portsrvkey', concat(@port,@svc_name))"/>
    <xsl:copy>
      <por><xsl:value-of select="@port"/></por>
      <service><xsl:value-of select="@svc_name"/></service>
      <report><xsl:value-of select="$key/ancestor::Report/@name"/></report>
    </xsl:copy>
  </xsl:template>

</xsl:transform>

XML Output

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:cm="http://www.nessus.org/cm">
   <ReportItem>
      <por>0</por>
      <service>general</service>
      <report>FAKEDB FAKEAPP</report>
   </ReportItem>
   <ReportItem>
      <por>1122</por>
      <service>availant-mgr?</service>
      <report>FAKEDB FAKEAPP</report>
   </ReportItem>
   <ReportItem>
      <por>1122</por>
      <service>ssh</service>
      <report>FAKEDB FAKEAPP</report>
   </ReportItem>
   <ReportItem>
      <por>47001</por>
      <service>www</service>
      <report>FAKEAPP</report>
   </ReportItem>
   <ReportItem>
      <por>49152</por>
      <service>dce-rpc</service>
      <report>FAKEAPP</report>
   </ReportItem>
   <ReportItem>
      <por>139</por>
      <service>smb</service>
      <report>FAKEAPP</report>
   </ReportItem>
</root>

You can then use Excel's Workbooks.OpenXML method to import into spreadsheet:

Workbooks.OpenXML "C:\Path\To\Transformed\Output.xml", , xlXmlLoadImportToList

answered Apr 22, 2016 at 4:27

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean Perryman Over a year ago

I've been avoiding responding in hopes I could figure this out and I just can't. The sample you provide does work except it isn't grabbing the FQDN for each occurrence and placing it into the <report> field you defined. I would be good using the ReportHost name (IP Address) too, but even switching to that still only results in one occurrence. Thanks. Sean.

Parfait Over a year ago

I'm used to OPs who go ghost! But I'm not understanding as I used your posted snippet. Does report tag result empty? Possibly your actual XML is not the same as posted. Also, how are you running the XSLT? VBA can use MSXML.

Collectives™ on Stack Overflow

Looping XML into Single Excel Cell using XSLT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related