XSL help

[gaboo]

Hi, I am fairly new to XML and XSL. I tried reading through a few online tutorials, but have been unable to figure out the best way to transform my xml correctly. Below is a part of my sample XML:

<row>
   <row_element column="0" property_name="EventTypeName">Start</row_element>
   <row_element column="1" property_name="ObjectName">Process001</row_element>
   <row_element column="2" property_name="DateStamp">17-Jan-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
</row>
<row>
   <row_element column="0" property_name="EventTypeName">Promote</row_element>
   <row_element column="1" property_name="ObjectName">Process001</row_element>
   <row_element column="2" property_name="DateStamp">21-Jan-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = GoTask ~ User = floor_manager5 ~ Comments: = Testing</row_element>
</row>
<row>
   <row_element column="0" property_name="EventTypeName">Finish</row_element>
   <row_element column="1" property_name="ObjectName">Process001</row_element>
   <row_element column="2" property_name="DateStamp">27-Jan-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = FinishTask ~ User = floor_cleaup18 ~ Comments: = Testing</row_element>
</row>

<row>
   <row_element column="0" property_name="EventTypeName">Start</row_element>
   <row_element column="1" property_name="ObjectName">Process022</row_element>
   <row_element column="2" property_name="DateStamp">5-FebJan-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
</row>
<row>
   <row_element column="0" property_name="EventTypeName">Promote</row_element>
   <row_element column="1" property_name="ObjectName">Process022</row_element>
   <row_element column="2" property_name="DateStamp">7-Feb-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = GoTask ~ User = floor_manager15 ~ Comments: = Testing</row_element>
</row>
<row>
   <row_element column="0" property_name="EventTypeName">Finish</row_element>
   <row_element column="1" property_name="ObjectName">Process022</row_element>
   <row_element column="2" property_name="DateStamp">15-Feb-2011</row_element>
   <row_element column="3" property_name="Data">job_name = Process02201 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = FinishTask ~ User = floor_cleaup29 ~ Comments: = Testing</row_element>
</row>

Sample output:

Object Responsible User Start Date Finished Date
Package123 floor_manager5 17-Jan-2011 27-Jan-2011
Package999 floor_manager15 7-Feb-2011 15-Feb-2011

Object column: get the root_object value if the event is 'Start' and has an object_name value of 'Ready to Load'

Responsible User column: get the user value if root_object value matches the root_object value found in the object column and if event is 'Promote' and has an object_name value of 'Ready to Load'

Start Date column: get the DateStamp value from the same 'Start' event from Object column

Finish Date column: get DateStamp value if root_object value matches the root_object value found in the object column and if event is 'Finish' and has an object_name value of 'Ready to Load'

So far I got to list the Object Column, but don't know what to do next to process the rest:

<xsl:template name="genReport">

<xsl:for-each select="row"> 
<xsl:if test="(row_element[@column='0']/text()='Start') and (substring-before(substring-after(row_element[@column='3']/text(),'object_name = '),' ~')='Ready to Load')">
<Row>
<Cell ss:StyleID="s84">
<Data ss:Type="String">
<xsl:value-of select="substring-before(substring-after(row_element[@column='3']/text(),'root_object = '),' ~')"/>
</Data>
</Cell>
</Row>
</xsl:if>
</xsl:for-each>
          
</xsl:template>

Please help and thank you in advance.

[xml_looser]

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output indent="yes" method="html"/>
	<xsl:variable name="rowdata" select="root/row"/>
	<xsl:template match="/">
		<xsl:apply-templates select="root"/>
	</xsl:template>
	<xsl:template match="root">
		<html>
			<table>
			<style>
			table{
			border: 2px solid black;
			}
            th,td{
			border: 2px solid black;
			padding: 15px;
			}
			th{
            background-color:#ff9900;
			}

			</style>
				<tr>
					<th>Object Responsible</th>
					<th>User</th>
					<th>Start Date</th>
					<th>Finished Date</th>
				</tr>
				<xsl:apply-templates select="row"/>
			</table>
		</html>
	</xsl:template>
	<xsl:template match="row">
	</xsl:template>
	<xsl:template match="row[position() mod 3 = 0]">
		<xsl:apply-templates select="row_element"/>
	</xsl:template>
	<xsl:template match="row_element">
		<xsl:choose>
			<xsl:when test="position() mod 4 = 0">
				<tr>
					<td>
						<xsl:value-of select="substring-before(substring-after(.,'root_object = '),' ')"/>
					</td>
					<td>
						<xsl:value-of select="substring-before(substring-after(../preceding-sibling::row[1],'User = '),' ')"/>
					</td>
					<td>
						<xsl:value-of select="../preceding-sibling::row[2]/row_element[3]"/>
					</td>
					<td>
						<xsl:value-of select="preceding-sibling::row_element[1]"/>
					</td>
				</tr>
			</xsl:when>
		</xsl:choose>
	</xsl:template>
</xsl:stylesheet>

[iceandrews]

I'm going to assume that you're only able to use XSLT 1.0. This is actually a really complicated grouping problem, because of the way your data is organized in the input document. You need to be able to group the sets of rows together that belong to the same Process##. In order to do this in XSLT 1.0, you need to implement what's called Muenchian Method. (Here's a link to the details of that. http://www.jenitennison.com/xslt/grouping/muenchian.html ).

Once you have things grouped together, this becomes relatively easy. I would take the time to explain all of this, but you're going to have to figure it out on your own and adapt it to your needs. I've modified your input document slightly and I've produced a simple html table to show the result.

INPUT

<?xml version="1.0"?>
<rows>
	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">17-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">21-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = GoTask ~ User = floor_manager5 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">27-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ root_object = Package123 ~ task_type = FinishTask ~ User = floor_cleaup18 ~ Comments: = Testing</row_element>
	</row>

	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">5-FebJan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">7-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = GoTask ~ User = floor_manager15 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">15-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process02201 ~ object_name = Ready to Load ~ root_object = Package999 ~ task_type = FinishTask ~ User = floor_cleaup29 ~ Comments: = Testing</row_element>
	</row>
</rows>

XSLT

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="html" indent="yes"/>

	<xsl:key name="rowsbyObject" match="row" use="row_element[@property_name = 'ObjectName']"/>

	<xsl:template match="/">
		<html>
			<body>
				<table>
					<tr>
						<td>Object</td>
						<td>User</td>
						<td>Start</td>
						<td>Finish</td>
					</tr>
					<xsl:apply-templates select="rows"/>
				</table>
			</body>
		</html>
	</xsl:template>

	<xsl:template match="rows">
		<xsl:for-each select="row[count(. | key('rowsbyObject', row_element[@property_name = 'ObjectName'])[1]) = 1]">
			<xsl:variable name="getStartData" select="key('rowsbyObject', row_element[@property_name = 'ObjectName'])/row_element[@property_name = 'EventTypeName'][. = 'Start']/../row_element[@property_name = 'Data']"/>
			<xsl:variable name="getPromoteData" select="key('rowsbyObject', row_element[@property_name = 'ObjectName'])/row_element[@property_name = 'EventTypeName'][. = 'Promote']/../row_element[@property_name = 'Data']"/>
			<xsl:variable name="getFinishData" select="key('rowsbyObject', row_element[@property_name = 'ObjectName'])/row_element[@property_name = 'EventTypeName'][. = 'Finish']/../row_element[@property_name = 'Data']"/>
			<tr>
				<td>
					<xsl:if test="(normalize-space(substring-before(substring-after($getStartData, 'object_name ='), '~')) = 'Ready to Load')">
						<xsl:value-of select="normalize-space(substring-before(substring-after($getStartData, 'root_object ='), '~'))"/>
					</xsl:if>
				</td>
				<td>
					<xsl:if test="(normalize-space(substring-before(substring-after($getPromoteData, 'object_name ='), '~')) = 'Ready to Load')">
						<xsl:value-of select="normalize-space(substring-before(substring-after($getPromoteData, 'User ='), '~'))"/>
					</xsl:if>
				</td>
				<td>
					<xsl:value-of select="row_element[@property_name = 'DateStamp'][../row_element[@property_name = 'EventTypeName'] = 'Start']"/>
				</td>
				<td>					
					<xsl:if test="(normalize-space(substring-before(substring-after($getPromoteData, 'object_name ='), '~')) = 'Ready to Load')">
						<xsl:value-of select="$getFinishData/../row_element[@property_name = 'DateStamp']" />						
					</xsl:if>
				</td>
			</tr>
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

OUTPUT

<html>
	<body>
		<table>
			<tr>
				<td>Object</td>
				<td>User</td>
				<td>Start</td>
				<td>Finish</td>
			</tr>
			<tr>
				<td>Package123</td>
				<td>floor_manager5</td>
				<td>17-Jan-2011</td>
				<td>27-Jan-2011</td>
			</tr>
			<tr>
				<td>Package999</td>
				<td>floor_manager15</td>
				<td>5-FebJan-2011</td>
				<td>15-Feb-2011</td>
			</tr>
		</table>
	</body>
</html>

[gaboo]

Thanks xml_looser and iceandrews for the quick help. From reviewing the two solutions, I think iceandrews example is better suited for my needs due to a lot of groupings. However, I will try both and report back later.

[gaboo]

iceandrews,

seems like your sample code works best for me. however, I did notice an issue. if I move the section of nodes related to EventType=Start after the Promote or Finish, it does not show the Start date.

Below is my sample input:

<?xml version="1.0"?>
<rows>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">21-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = GoTask ~ User = floor_manager5 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">27-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = FinishTask ~ User = floor_cleaup18 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">17-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>


	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">15-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process02201 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = FinishTask ~ User = floor_cleaup29 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">7-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = GoTask ~ User = floor_manager15 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">5-FebJan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>	
</rows>

[iceandrews]

My fault, I thought I had made it order independent. Obviously, I didn't take the time to really give this the testing it should have. That's the kind of testing and fixing that I leave up to you! But here's a fix anyway :)

Change the third <td> change the line from:

<xsl:value-of select="row_element[@property_name = 'DateStamp'][../row_element[@property_name = 'EventTypeName'] = 'Start']"/>

Into!

<xsl:value-of select="$getStartData/../row_element[@property_name = 'DateStamp']" />

That will fix it.

[gaboo]

I noticed that using the variable getPromoteData has some issues if the xml has multiple Promote event types. There can be multiple Promote events with all the same ObjectName and Data. The only difference is the DateStamp. However, it seems like it's already broken when the variable is being set.

Just rechecked and I did notice that the values for Comments may be different. But DateStamp and Comments can't be too useful since they aren't always unique.

If I can expose another unique value under the Data element, is there a way for me to filter for that value before setting the getPromoteData?

For example:

<?xml version="1.0"?>
<rows>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">21-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = GoTask ~ User = floor_manager5 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">22-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = GoTask ~ User = floor_manager5 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">27-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = FinishTask ~ User = floor_cleaup18 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process001</row_element>
		<row_element column="2" property_name="DateStamp">17-Jan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process001 ~ object_name = Ready to Load ~ 
root_object = Package123 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>


	<row>
		<row_element column="0" property_name="EventTypeName">Finish</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">15-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process02201 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = FinishTask ~ User = floor_cleaup29 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Promote</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">7-Feb-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = GoTask ~ User = floor_manager15 ~ Comments: = Testing</row_element>
	</row>
	<row>
		<row_element column="0" property_name="EventTypeName">Start</row_element>
		<row_element column="1" property_name="ObjectName">Process022</row_element>
		<row_element column="2" property_name="DateStamp">5-FebJan-2011</row_element>
		<row_element column="3" property_name="Data">job_name = Process022 ~ object_name = Ready to Load ~ 
root_object = Package999 ~ task_type = DoTask ~ User = floor1 ~ Comments: = Starting</row_element>
	</row>	
</rows>

[iceandrews]

That makes things a little more complicated. How do you know which promotion event to use in your output? You say the comments are different on each. Well you want the User for for those, what if they are different? How do you know which Promotion event Data comment you want to pull the data from?

There are going to be multiple promotion events that match your requirement of "Responsible User column: get the user value if root_object value matches the root_object value found in the object column and if event is 'Promote' and has an object_name value of 'Ready to Load'".

If it doesn't matter WHICH promotion event that you pull from, since you don't care about the Promotion date, and all you care about is the user. For example, the user is going to be the same across all promotion events. You can just use the FIRST one that you come too.

Change the variable to something like this (notice the [1] predicate)

<xsl:variable name="getPromoteData" select="key('rowsbyObject', row_element[@property_name = 'ObjectName'])/row_element[@property_name = 'EventTypeName'][. = 'Promote'][1]/../row_element[@property_name = 'Data']"/>

If there's another unique identifier that that's distinguishes Promote rows, that could be added to the variable as well to select the one you're looking for.

[gaboo]

I'm not sure if I can find a unique identifier yet. Need to ask someone first.

For example, if Comments was unique. Say I want to filter on Promote event with Comments = 'Approved'. How would I add it to the existing variable?
or should I create another variable with the following:

(normalize-space(substring-after($getPromoteData, 'Comments = ')) = 'Approved')

or should it be something like this:

<xsl:variable name="getPromoteData" select="key('rowsbyObject', row_element[@property_name = 'ObjectName'])/row_element[@property_name = 'EventTypeName'][. = 'Promote'][1]/../row_element[@property_name = 'Data']/(normalize-space(substring-after(., 'Comments =')) = 'Approved')"/>

Thanks

[iceandrews]

Well, this is starting to get pretty detailed and while I'd love to be able to answer every question, I just don't have the time. Plus, when it starts to get into waters where I'm writing a total solution for someone, it's too much like consulting. (which is how I make a living!)

I don't want to seem like I don't care, I do, but it's just a matter of time and effort. There should be enough between the various options that have been presented for you to be able to figure something out that meets your needs. (Once they're defined) I don't have the time to learn every requirement and build a complete solution that's full proof. I hope everything so far has been helpful and provided new insight to ways to use XSLT.