XML to XML with XSLT

[stljim]

Hello everyone,

I'm _very_ new to xml, especially converting using an xslt. Here's my source XML generated by Eeye Retina. This data is being used by MS Access and I don't need all the data from the original XML. When I try to run the MS Access app I get an import error saying there was an error loading the transform file. A name contained an invalid character.

The basic tree of the original XML is:

ScanJob
    hosts
      host1
        host1 detail 1
        host1 detail 2
        host1 detail X
        audit1
            audit1 detail 1
            audit1 detail 2
            audit1 detail X
        auditN
            auditN detail 1
            auditN detail 2
            auditN detail X
      hostN
        hostN detail 1
        hostN detail 2
        hostN detail X
        audit1
            audit1 detail 1
            audit1 detail 2
            audit1 detail X
        auditN
            auditN detail 1
            auditN detail 2
            auditN detail X
    metrics
      metric detail 1
      metric detail 2
      metric detail X

Here's the sample XML source:

<scanJob>
	<hosts>
		<host>
			<ip>192.168.1.1</ip>
			<netBIOSName>Host1</netBIOSName>
			<dnsName>host1.mynet.net</dnsName>
			<mac>00:xx:55:xx:68:xx</mac>
			<os>Windows Server</os>
			<audit>
				<rthID>137xx</rthID>
				<cve>N/A</cve>
				<cce>N/A</cce>
				<iav>N/A</iav>
				<name>Name of the audit</name>
				<description> Description of Audit</description>
				<date>12/21/2010</date>
				<sevCode>Category II</sevCode>
				<risk>Medium</risk>
				<pciLevel>5 (Urgent)</pciLevel>
				<cvssScore>N/A</cvssScore>
				<fixInformation>Fix to correct the issue</fixInformation>
			</audit>
			<audit>
				<rthID>138xx</rthID>
				<cve>CVS-0001</cve>
				<cce>N/A</cce>
				<iav>2010-A-0000</iav>
				<name>Name of Audit</name>
				<description>Description of Audit</description>
				<date>12/21/2010</date>
				<sevCode>Category II</sevCode>
				<risk>High</risk>
				<pciLevel>5 (Urgent)</pciLevel>
				<cvssScore>9.3 [AV:N/AC:M/Au:N/C:C/I:C/A:C]</cvssScore>
				<fixInformation>Fix to correct the finding.</fixInformation>
			</audit>
		</host>
		<host>
			<ip>192.168.1.2</ip>
			<netBIOSName>Host2</netBIOSName>
			<dnsName>host2.mynet.net</dnsName>
			<mac>00:xx:55:xx:51:xx</mac>
			<os>Windows Server 2008</os>
			<audit>
				<rthID>137xx</rthID>
				<cve>N/A</cve>
				<cce>N/A</cce>
				<iav>N/A</iav>
				<name>Name of Audit</name>
				<description>Description of Audit</description>
				<date>12/21/2010</date>
				<sevCode>Category II</sevCode>
				<risk>Medium</risk>
				<pciLevel>5 (Urgent)</pciLevel>
				<cvssScore>N/A</cvssScore>
				<fixInformation>Fix to correct the finding.</fixInformation>
			</audit>
			<audit>
				<rthID>13xxx</rthID>
				<cve>CVE-2010-0000</cve>
				<cce>N/A</cce>
				<iav>2010-A-0000</iav>
				<name>Name of Audit</name>
				<description>Description of Audit</description>
				<date>12/21/2010</date>
				<sevCode>Category II</sevCode>
				<risk>High</risk>
				<pciLevel>5 (Urgent)</pciLevel>
				<cvssScore>9.3 [AV:N/AC:M/Au:N/C:C/I:C/A:C]</cvssScore>
				<fixInformation>Fix to correct the finding.</fixInformation>
			</audit>
		</host>
	</hosts>
	<metrics>
		<jobName>MyScan1</jobName>
		<fileName>C:\Program Files\MyScan1.rtd</fileName>
		<scannerVersion>5.11.12</scannerVersion>
		<auditsRevision>23xx</auditsRevision>
		<credentials>XXE3XX92XXE2XX86XXECXX86XX11XX45</credentials>
		<auditGroups>IAVA</auditGroups>
		<addressGroups>MyAuditGroup</addressGroups>
		<ipRanges>N/A</ipRanges>
		<attempted>22</attempted>
		<scanned>22</scanned>
		<noAdmin>0</noAdmin>
		<start>12/21/2010 11:43:30 AM</start>
		<duration>0d 0h 8m 16s</duration>
	</metrics>
</scanJob>

Here's the XSL transform file I'm using:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" standalone="yes" encoding="iso-8859-1" indent="yes"/>

	<xsl:template match="scanJob">
		<hosts>
			<xsl:apply-templates select="hosts"/>
		</hosts>
		<metrics>
			<xsl:apply-templates select="metrics"/>
		</metrics>
	</xsl:template>


	<xsl:template match="hosts">
		<host>
			<xsl:apply-templates select="host"/>
		</host>
	</xsl:template>


	<xsl:template match="audit">
		<audit>
			<rthID><rthID="{rthID}"/></rthID>
			<name><name="{name}"/></name>
			<sevCode><sevCode="{sevCode}"/></sevCode>
			<risk><risk="{risk}"/></risk>
			<fixInformation><fixInformation="{fixInformation}"/></fixInformation>
		</audit>
	</xsl:template>	


	<xsl:template match="host">
		<ip><ip="{ip}"/></ip>
		<netBIOSName><netBIOSName="{netBIOSName}"/></netBIOSName>
		<dnsName><dnsName="{dnsName}"/></dnsName>
		<mac><mac="{mac}"/></mac>
		<os>os="{os}"/></os>
		<xsl:apply-templates select="audit"/>
	</xsl:template>		

	<xsl:template match="metrics">
		<jobName><jobName="{jobName}"/></jobName>
		<fileName><fileName="{fileName}"/><fileName>
		<scannerVersion><scannerVersion="{scannerVersion}"/></scannerVersion>
		<auditsRevision><auditsRevision="{auditsRevision}"/></auditsRevision>
		<auditGroups><auditGroups="{auditGroups}"/></auditGroups>
		<addressGroups><addressGroups="{addressGroups}"/></addressGroups>
		<ipRanges><ipRanges="{ipRanges}"/></ipRanges>
	</xsl:template>

</xsl:stylesheet>

I don't have any output because I'm using MS Access. Which brings up another question of a better tool to use to 'debug' this issues than the behind-the-scenes stuff Access is doing.

Anyway, any help/suggestions/pointers would be most greatly appreciated.

Thanks,
Jim

[iceandrews]

I've not used Access to import XSLT transformation, so I can't really speak to that problem. Sounds like either the filename or the document itself has invlad characters inside of it.

If you're using Access, it's likely that you're using the MSXML processor to run your transformations. In that case, you can use Visual Studio to create, run and test your transformations. However, I'd really recommend a true XML development suite. Stylus Studio (www.stylusstudio.com) is probably the best for pure XSLT processing and testing. Altova XML Spy (http://www.altova.com/xml-editor/) is also another good IDE. Both of those products have great tools for editing, running debugging and testing your transformations. Obviously neither is free :) If you're looking a "free" XMl editor, I'd recommend EditiX Lite. (http://free.editix.com/). It's no where close to the other two, but it might do in a pinch if this is something you don't want to spend money on.

Feel free to shoot any other questions as well.

[stljim]

I've not used Access to import XSLT transformation, so I can't really speak to that problem. Sounds like either the filename or the document itself has invlad characters inside of it.

If you're using Access, it's likely that you're using the MSXML processor to run your transformations. In that case, you can use Visual Studio to create, run and test your transformations. However, I'd really recommend a true XML development suite. Stylus Studio (www.stylusstudio.com) is probably the best for pure XSLT processing and testing. Altova XML Spy (http://www.altova.com/xml-editor/) is also another good IDE. Both of those products have great tools for editing, running debugging and testing your transformations. Obviously neither is free :) If you're looking a "free" XMl editor, I'd recommend EditiX Lite. (http://free.editix.com/). It's no where close to the other two, but it might do in a pinch if this is something you don't want to spend money on.

Feel free to shoot any other questions as well.

Thank you for the suggested tools. I've been reading the w3schools tutorial but it seems, as with just about everything, there are so many ways to accomplish the same thing. If anyone has some good pointers to get the data transformed I would be greatly appreciated.

Thanks,
Jim

[iceandrews]

If you post what you want your OUTPUT XML document to look like, based on your sample input document, I'll be happy to look at it.