Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

XML import - multiple children types to FM Rows ?

ade234

By ade234
in Importing & Exporting

 Share

This topic is 4537 days old. Please don't post here. Open a new topic instead.

Recommended Posts

ade234 Rookie

ade234

Posted
Posted

Hi,

I have a xml that looks a little like this:


<sequence>

<media>

<video>

<track>

<clipitem>

	 <name>1<name>

	 <in>123</in>

</clipitem>

<clipitem>

	 <name>2<name>

	 <in>234</in>

	 <comment>

	 <comment1>nnn</comment1>

	 </comment>

</clipitem>

</track>

<track>

<clipitem>

	 <name>3<name>

	 <in>345</in>

</clipitem>

<clipitem>

	 <name>4<name>

	 <in>456</in>

</clipitem>

<transitionitem>

	 <name>dissolve</name>

	 <duration>12</duration>

</transitionitem>

<clipitem>

	 <name>5<name>

	 <in>567</in>

</clipitem>

</track>

</video>

</media>

</sequence>






I am attempting to create a database that creates a new row for the following

items and looks like the below:



COLUMNS:   type	  /   name /  in   / comment	/  DisolveDuration   /  



track

clipitem / 1 / 123

clipitem / 2 / 234 / nnn

track

clipitem / 3 / 345

clipitem / 4 / 456

transitionitem / dissolve /  /  / 12

clipitem / 5 / 567



I am using the below stylesheet but can't find a way to get all clipitems and tracks and transition items to come up in order in rows.

The current stylesheet adds all clipitems only.






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

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

<xsl:template match='/'>

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

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="11/13/2002" NAME="Filemaker Pro" VERSION="6.0V4"/>

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

<METADATA>

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

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="in" TYPE="NUMBER"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="comment" TYPE="NUMBER"/>





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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="DissolveDuration" TYPE="NUMBER"/>



</METADATA>

<RESULTSET FOUND="">

<xsl:for-each select="./xmeml/sequence/media/video/track/clipitem">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="./preceding-sibling::track /></DATA></COL>

<COL><DATA><xsl:value-of select="./name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./in" /></DATA></COL>

<COL><DATA><xsl:value-of select="./comment/comment1" /></DATA></COL>





<COL><DATA><xsl:value-of select="./transitionitem/effect/name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./transitionitem/duration" /></DATA></COL>



</ROW>

</xsl:for-each>

</RESULTSET></FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

Any assistance would be most appreciated,

Ade

comment Grand Master

comment

Posted
Posted (edited)

I cannot understand the format of your intended table (or perhaps it should be tables?).

---

EDIT:

Your XML example is not a valid XML document, and it does not match your stylesheet. It pays to be meticulously exact with these things.

Edited by comment
ade234 Rookie

ade234

Posted
  • Author
Posted

Sorry,

It is meant to represent a single filemaker table layout of rows and columns. Is there a way to better represent this?

updated references :)

Source XML


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

<!DOCTYPE xmeml>

<xmeml version="5">

<sequence>

<media>

  <video>

   <track>

    <clipitem>

	 <name>1<name>

	 <in>123</in>

    </clipitem>

    <clipitem>

	 <name>2<name>

	 <in>234</in>

	 <comment>

	  <comment1>nnn</comment1>

	 </comment>

    </clipitem>

   </track>

   <track>

    <clipitem>

	 <name>3<name>

	 <in>345</in>

    </clipitem>

    <clipitem>

	 <name>4<name>

	 <in>456</in>

    </clipitem>

    <transitionitem>

	 <name>dissolve</name>

	 <duration>12</duration>

    </transitionitem>

    <clipitem>

	 <name>5<name>

	 <in>567</in>

    </clipitem>

   </track>

  </video>

</media>

</sequence>  

</xmeml>






and XSL 




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

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

<xsl:template match='/'>

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

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="11/13/2002" NAME="Filemaker Pro" VERSION="6.0V4"/>

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

<METADATA>

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

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="in" TYPE="NUMBER"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="comment" TYPE="NUMBER"/>

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="DissolveDuration" TYPE="NUMBER"/>

</METADATA>

<RESULTSET FOUND="">

<xsl:for-each select="./xmeml/sequence/media/video/track/clipitem">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="./preceding-sibling::track /></DATA></COL>

<COL><DATA><xsl:value-of select="./name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./in" /></DATA></COL>

<COL><DATA><xsl:value-of select="./comment/comment1" /></DATA></COL>

<COL><DATA><xsl:value-of select="./transitionitem/name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./transitionitem/duration" /></DATA></COL>

</ROW>

</xsl:for-each>

</RESULTSET></FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

Thanks,

Ade

comment Grand Master

comment

Posted
Posted

OK, so do you want to create a new record for each item - whether a clipitem or a transitionitem? That would be easy - but the two types don't have the same attributes, so how do you want to handle that? AFAICT, a clipitem needs fields for Name, In and Comment, while a transitionitem has Name and Duration. You could let your target table have all of the fields and fill out only the relevant ones according to the type of record (which probably should be a field on its own).

I see you also have a field for track - but what would go into it?

ade234 Rookie

ade234

Posted
  • Author
Posted

Thanks comment,

Yes I do want to create a new record for each item, whether track, clipitem or transitionitem.

My problem is getting the right nomenclature for the xsl template to be able to do this. Currently it is only populating the records with the clipitem values.

<xsl:for-each select="./xmeml/sequence/media/video/track/clipitem">

How do I adjust the template to encapsulate clipitems and transitionitems and tracks?

I have tried the below but it just leaves me with one mysterious event in the result DB.

<xsl:for-each select="./xmeml/sequence/media/video/track">

Also the track would not have any DB fields associated with it . That said i do need to know where and at what point the track events occur in relation to the other events.

src XML again....


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

<!DOCTYPE xmeml>

<xmeml version="5">

<sequence>

<media>

  <video>

   <track>

    <clipitem>

	 <name>1</name>

	 <in>123</in>

    </clipitem>

    <clipitem>

	 <name>2</name>

	 <in>234</in>

	 <comment>

	  <comment1>nnn</comment1>

	 </comment>

    </clipitem>

   </track>

   <track>

    <clipitem>

	 <name>3</name>

	 <in>345</in>

    </clipitem>

    <clipitem>

	 <name>4</name>

	 <in>456</in>

    </clipitem>

    <transitionitem>

	 <name>dissolve</name>

	 <duration>12</duration>

    </transitionitem>

    <clipitem>

	 <name>5</name>

	 <in>567</in>

    </clipitem>

   </track>

  </video>

</media>

</sequence>  

</xmeml>

Thanks,

Ade

ade234 Rookie

ade234

Posted
  • Author
Posted

After a bit more noodling i have come up with this that seems to work.

Im not sure if it can me made to be more robust, let me know if it can.

It is now adding tracks, clipitems and transitionitems in order of the XML. YAY


<xsl:for-each select="//clipitem | //transitionitem | //track">






in




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

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

<xsl:template match='/'>

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

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="11/13/2002" NAME="Filemaker Pro" VERSION="6.0V4"/>

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

<METADATA>

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

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="in" TYPE="NUMBER"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="comment" TYPE="NUMBER"/>

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="DissolveDuration" TYPE="NUMBER"/>

</METADATA>

<RESULTSET FOUND="">

<xsl:for-each select="//clipitem | //transitionitem | //track">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="./track /></DATA></COL>

<COL><DATA><xsl:value-of select="./name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./in" /></DATA></COL>

<COL><DATA><xsl:value-of select="./comment/comment1" /></DATA></COL>

<COL><DATA><xsl:value-of select="./transitionitem/name" /></DATA></COL>

<COL><DATA><xsl:value-of select="./transitionitem/duration" /></DATA></COL>

</ROW>

</xsl:for-each>

</RESULTSET></FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

comment Grand Master

comment

Posted
Posted

I don't think generating dummy track records is a good idea: they will lose any significance as soon as you perform a find and/or sort. I believe you should create records for items only (i.e. clipitems nad transitionitems) and group them by assigning a unique ID to each track - something along the lines of:


...

<xsl:for-each select="xmeml/sequence/media/video/track/*">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="generate-id(../.)"/></DATA></COL>

<COL><DATA><xsl:value-of select="name(.)"/></DATA></COL>

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

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

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

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

</ROW>

</xsl:for-each>

...

Note: the first field is TrackID, the second one is ClipType.

This topic is 4537 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept