Selecting correct values in XML with XSLT

[nycpost]

Hi All,

I have an XML I'm trying to transform via XSLT for import into FMP and running into an issue.

The XML has several "Events" and "Comments" listed. The Comments always follow the Events that they comment on. I'm trying to write an XSL that will put both the Event info and the following Comment into on the same record Row in the resulting FMP XML that I'm creating. The problem is I can't get the XSL to pull the correct value for each event. Does anyone know if this is possible?

Here's a snippet of the XML code:

<Event Num="2"  Type="Cut" Length="252" SourceCount="1" >

<Master><Reel></Reel>

<Start>

<Timecode Type="TC1">01:08:12:00</Timecode>

<Pullin></Pullin>

<Edgecode Type="Master" Film="Total Frames">0738+00</Edgecode>

<Frame>11808</Frame>

</Start>

<End>

<Timecode Type="TC1">01:08:22:11</Timecode>

<Pullin></Pullin>

<Edgecode Type="Master" Film="Total Frames">0753+11</Edgecode>

<Frame>12059</Frame>

</End>

</Master>

<Source>

<ClipName>82A-4</ClipName>

<MobID>060a2b340101010101010f00-13-00-00-00-{4d66b121-5faf-302d-060e2b347f7f2a80}</MobID>

<Start>

<Frame>1530468</Frame>

<Timecode Type="Start TC">17:42:49:12</Timecode>

<Timecode Type="Aux TC1">17:42:49:12</Timecode>

<Timecode Type="Aux TC2">17:42:49:12</Timecode>

<Edgecode Type="AuxInk" Film="35mm4p">00C04909-5654+04</Edgecode>

<Edgecode Type="DPX">-0002582</Edgecode>

</Start>

<End>

<Frame>1530719</Frame>

<Timecode Type="Start TC">17:42:59:23</Timecode>

<Timecode Type="Aux TC1">17:42:59:23</Timecode>

<Timecode Type="Aux TC2">17:42:59:23</Timecode>

<Edgecode Type="AuxInk" Film="35mm4p">00C04909-5669+15</Edgecode>

<Edgecode Type="DPX">-0002833</Edgecode>

</End>

<TapeName>C049</TapeName>

</Source>

</Event>

<Comment Type="Locator"><Color></Color>

<Master><Timecode Type="TC1">01:08:16:18</Timecode>

<Frame>11922</Frame>

</Master>

<Source><ClipName>82A-4</ClipName>

<Timecode Type="Start TC">17:42:54:06</Timecode>

<Timecode Type="Aux TC1">17:42:54:06</Timecode>

<Timecode Type="Aux TC2">17:42:54:06</Timecode>

</Source>

<Text>MA020	252 fr</Text></Comment>

 

And here's my current XSL:

 

<?xml version='1.0' encoding='UTF-8' ?>

<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>

<xsl:output version='1.0' encoding='UTF-8' indent='no' method='xml' />

<xsl:template match='/'>

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

  <ERRORCODE>0</ERRORCODE>

  <PRODUCT BUILD="" NAME="" VERSION=""/>

  <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a"/>

  <METADATA>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecordTCStart" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecordTCEnd" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecordFramesStart" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecordFramesEnd" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecordFramesDuration" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Clip_Name" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Tape" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SourceTCStart" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SourceTCEnd" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SourceFramesStart" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SourceFramesEnd" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Locator_Text" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="NLESequenceName" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SequenceTRTTC" TYPE="TEXT"/>

   <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SequenceTRTFrames" TYPE="TEXT"/>

  </METADATA>

  <RESULTSET FOUND="">

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

	<ROW MODID="0" RECORDID="0">

	<COL><DATA><xsl:value-of select="Master/Start/Timecode" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Master/End/Timecode" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Master/Start/Frame" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Master/End/Frame" /></DATA></COL>

	<COL><DATA><xsl:value-of select="@Length" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/ClipName" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/TapeName" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/Start/Timecode" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/End/Timecode" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/Start/Frame" /></DATA></COL>

	<COL><DATA><xsl:value-of select="Source/End/Frame" /></DATA></COL>

	<COL><DATA><xsl:value-of select="/FilmScribeFile/AssembleList/Events/Comment/Text"/></DATA></COL>

	<COL><DATA><xsl:value-of select="/FilmScribeFile/AssembleList/ListHead/Title"/></DATA></COL>

	<COL><DATA><xsl:value-of select="/FilmScribeFile/AssembleList/ListHead/MasterDuration/Timecode" /></DATA></COL>

	<COL><DATA><xsl:value-of select="/FilmScribeFile/AssembleList/ListHead/MasterDuration/FrameCount" /></DATA></COL>

	</ROW>

   </xsl:for-each>

  </RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

The section of the XSL that's giving me problems is the "/Comment/Text" part toward the end. It keeps pulling the first value it finds, but I need it to pull the value following each Event.

My full XML and XSL are both attached.

Thanks,

Mike

[comment]

My full XML and XSL are both attached.

Try again.

[nycpost]

Sorry about that. I forgot to hit Attach This File. Here ya go...

Archive.zip

[comment]

Well, I was going to suggest using the following-sibling axis, but I see that not every event has a comment - and apparently a single event could have multiple comments. How do you plan to deal with such situation? If I follow correctly the logic of the source XML, the comments should be in a separate table, and related to events by Master Start ≤ Comment TC/Frame ≤ Master End.

[nycpost]

OK, so I would need to script it so the Events are brought into one table and the Comments into another and then link them via TC? That can work. But to confirm, there's no way to relate a subsequent Comment to the previous Event?

Thanks, Comment.

Mike

[comment]

But to confirm, there's no way to relate a subsequent Comment to the previous Event?

I think you mean "include a subsequent Comment in the previous Event"? It is possible - but you must choose between getting only the immediately following comment or some other way of stuffing multiple comments into a single event.

[nycpost]

I think you mean "include a subsequent Comment in the previous Event"? It is possible - but you must choose between getting only the immediately following comment or some other way of stuffing multiple comments into a single event.

Oh, OK. Yes, the XML would always only have 1 comment following each event, so multiples won't be a problem.

[comment]

If you are sure of that, try:

<xsl:value-of select="following-sibling::*[1][name()='Comment']/Text"/>

[nycpost]

Comment, that works great. Thanks so much! Your knowledge seriously knows no bounds!