XSLT Syntax Problem - One extra level

Question

Hi

I have a frustrating issue which would be dead simple to someone who understands this..

I am trying to create an XSL / XSLT file to printout the Windows Event Log from its XML form into CSV format.  The default CSV format from the Event Log EXCLUDES the Eventdata which is next to useless.
The default Excel handling of the XML repeats the same record for every EventData entry !!
I want to printout the <System> info and the <EventData>

The web has no end of examples for books etc where you have
<library>
   <books>
        tags in here
   </books>

In the event view case there is one extra level, e.g.

<Events>
   <Event>
        <System>
             tags in here
        </System>
        <EventData>
             tags in here
        </EventData>
    </Event>

No matter what i try, i can't reference the internal data. I want to be able to do an "all fields" report, some "*" syntax and also a more tailored "specific name"
Note: The <EventData> has the ONE repeated field <DATA> (this being why Excel doesn't know what to do with it)

In the example below the output created is 'Start of macro"
I have tried about 20 different variants of match= and select=

>> Please, what is the magic trick ?

XSL[T]

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="text"/>

<xsl:template match="/">

	<xsl:text>Start of macro</xsl:text>

	<xsl:for-each select ="Events/Event">
		<xsl:text>test </xsl:text>

		<xsl:for-each select ="System">

			<xsl:value-of select ="."/>
			<xsl:text>&#x44;</xsl:text>
		</xsl:for-each>
		<xsl:text>&#xD;&#xa;</xsl:text>
	</xsl:for-each>
  ### Example is missing <EventData> Handling
</xsl:template>
 
</xsl:stylesheet>

INPUT

  <?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
- <Events>
- 	<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- 		<System>
  			<Provider Name="Security" /> 
  			<EventID Qualifiers="0">675</EventID> 
  			<Level>0</Level> 
  			<Task>9</Task> 
  			<Keywords>0x90000000000000</Keywords> 
 			 <TimeCreated SystemTime="2012-02-15T01:04:14.000000000Z" /> 
 			 <EventRecordID>90813578</EventRecordID> 
  			<Channel>C:\Temp\Ad\logs.evt</Channel> 
  			<Computer>NR-DC-ABC01</Computer> 
  			<Security UserID="S-1-5-18" /> 
  		</System>
- 		<EventData>
			  <Data>ABCLAV52$</Data> 
 			 <Data>%{S-1-5-21-2108032-2062508577-10721152-28962}</Data> 
			  <Data>krbtgt/ABC.COM</Data> 
 			 <Data>0x0</Data> 
 			 <Data>0x19</Data> 
 			 <Data>10.176.17.159</Data> 
  		</EventData>
 	 </Event>

Thanks !!

Answer 1

One problem in your XML input sample is the default namespace declaration xmlns="http://schemas.microsoft.com/win/2004/08/events/event" on the "Event" element. Your stylesheet needs to take that into account but does not do it at all.

In your stylesheet you need to bind a prefix to that namespace and then you need to use that prefix to qualify element names when you want to select element in that namespace e.g.

  <xsl:stylesheet

     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

     xmlns:evt="http://schemas.microsoft.com/win/2004/08/events/event"

     version="1.0">

     ...

     <xsl:for-each select="Events/evt:Event">

        ...

        <xsl:for-each select="evt:System">

        </xsl:for-each>

        <xsl:for-each select="evt:EventData/evt:Data">...</xsl:for-each>

You haven't really said which output you want for your sample XML input so I have not tried to output any data above, instead I have simply shown how to access the elements in the namespace. If that hint alone does not help you write the stylesheet to create your needed CSV format then please post the sample output you want for the sample input you have shown.

---

MVP Data Platform Development My blog

Answer 2

Thanks Martin

My guess is you are on the money, i'll try this later as it is beatifull sunday day here in oz

I am wanting to create a CSV format so am confortable on that side.

Cheers

Answer 3

Martin

Want to go for bonus points on how to get the values for tags like

 <TimeCreated SystemTime="2012-02-15T01:04:14.000000000Z" />

This works for normal tags but not tags which seem to have "meta" data ?

(now i found an example       <xsl:value-of select="contact/@type"/> which uses the /@name to get inside, but can you find the /@SystemTime attribute without having it hard coded ?

NB: <xsl:value-of select ="TimeCreated/@SystemTime"/> returns blank even though it has a value)

		= <for-each select="evt:System"></for-each><for-each select="*"></for-each><value-of select="."></value-of>: </value-of><//value-of></for-each><//for-each></for-each><//for-each><text></text></text><//text>

Thanks

Answer 4

In your sample "TimeCreated" is an element node which has an attribute named "SystemTime". To access an attribute in XPath you use

  @attributeName

or

  attribute::attributeName

(where the "attribute" is meant literally as the so called XPath axis and where you of course need to adapt the "attributeName" part) so your attempt with

  TimeCreated/@SystemTime

is fine as far as the attribute is concerned, for the element I suspect it is too in that namespace as the other elements so you need

  evt:TimeCreated/@SystemTime

---

MVP Data Platform Development My blog

Answer 5

Thanks Martin