xslt - XLST Combine Sibling Nodes into 1 Record -

trying learn how use xls/xlst importing data filemaker. have basics down, need optimization.

currently have 11 sets of data splintered siblings need combine single record during import process. right now, use 11 separate xsl files import data in 11 separate imports each file focusing on specific set of data (fire count, hit count, score, etc). terribly inefficient, lack know-how work in more detail xlst , combine fragmented data 1 record.

an example of data i'm working follows:

<character_list limit="0" returned="2" milliseconds="0"> <character id="5428010618020696081" active_profile_id="15" faction_id="3" head_id="1" title_id="0"> <stats> <weapon_stat_list> <weapon_stat item_id="25003" last_save="1354544215" last_save_date="2012-12-03 14:16:55.0" stat_name="weapon_fire_count" value="108" vehicle_id="0"/> <weapon_stat item_id="25003" last_save="1354544215" last_save_date="2012-12-03 14:16:55.0" stat_name="weapon_hit_count" value="60" vehicle_id="0"/> <weapon_stat item_id="125" last_save="1354564613" last_save_date="2012-12-03 19:56:53.0" stat_name="weapon_fire_count" value="7708" vehicle_id="0"/> <weapon_stat item_id="125" last_save="1354564613" last_save_date="2012-12-03 19:56:53.0" stat_name="weapon_play_time" value="13406" vehicle_id="0"/> <weapon_stat item_id="125" last_save="1354564613" last_save_date="2012-12-03 19:56:53.0" stat_name="weapon_score" value="62012" vehicle_id="0"/> <weapon_stat item_id="1259" last_save="1354573520" last_save_date="2012-12-03 22:25:20.211913" stat_name="weapon_fire_count" value="22" vehicle_id="0"/> </weapon_stat_list> </stats> </character> <character id="5428010618040144225" active_profile_id="6" faction_id="2" head_id="1" title_id="80"> <stats> <weapon_stat_list> <weapon_stat item_id="126" last_save="1353442416" last_save_date="2012-11-20 20:13:36.0" stat_name="weapon_fire_count" value="130" vehicle_id="0"/> <weapon_stat item_id="126" last_save="1353442416" last_save_date="2012-11-20 20:13:36.0" stat_name="weapon_play_time" value="336" vehicle_id="0"/> <weapon_stat item_id="126" last_save="1353442416" last_save_date="2012-11-20 20:13:36.0" stat_name="weapon_score" value="570" vehicle_id="0"/> <weapon_stat item_id="1260" last_save="1353442416" last_save_date="2012-11-20 20:13:36.099928" stat_name="weapon_fire_count" value="181" vehicle_id="0"/> <weapon_stat item_id="1260" last_save="1353442416" last_save_date="2012-11-20 20:13:36.100053" stat_name="weapon_play_time" value="471" vehicle_id="0"/> <weapon_stat item_id="1260" last_save="1353442416" last_save_date="2012-11-20 20:13:36.100189" stat_name="weapon_score" value="635" vehicle_id="0"/> <weapon_stat item_id="1261" last_save="1353447137" last_save_date="2012-11-20 21:32:17.0" stat_name="weapon_fire_count" value="321" vehicle_id="0"/> <weapon_stat item_id="1261" last_save="1353447137" last_save_date="2012-11-20 21:32:17.0" stat_name="weapon_play_time" value="442" vehicle_id="0"/> <weapon_stat item_id="1261" last_save="1353447137" last_save_date="2012-11-20 21:32:17.0" stat_name="weapon_score" value="1228" vehicle_id="0"/> </weapon_stat_list> </stats> </character> </character_list> 

unique identifiers each character weapon data record combination of character id, item id, , vehicle id. type of data in fragment determined "stat_name" attribute.

the gist of output need is:

<row modid="1" recordid="1" >     <col><data>5428010618020696081</data></col>     <col><data>25003</data></col>     <col><data>0</data></col>     <col><data>108</data></col>     <col><data>60</data></col>     <col><data></data></col> </row> <row modid="2" recordid="2" >     <col><data>5428010618020696081</data></col>     <col><data>125</data></col>     <col><data>0</data></col>     <col><data>7708</data></col>     <col><data></data></col>     <col><data>13406</data></col>     <col><data>62012</data></col> </row> <row modid="3" recordid="3" >     <col><data>5428010618020696081</data></col>     <col><data>1259</data></col>     <col><data>0</data></col>     <col><data>22</data></col>     <col><data></data></col>     <col><data></data></col>     <col><data></data></col> </row> <row modid="4" recordid="4" >     <col><data>5428010618040144225</data></col>     <col><data>126</data></col>     <col><data>0</data></col>     <col><data>130</data></col>     <col><data></data></col>     <col><data>336</data></col>     <col><data>570</data></col> </row> <row modid="5" recordid="5" >     <col><data>5428010618040144225</data></col>     <col><data>1260</data></col>     <col><data>0</data></col>     <col><data>181</data></col>     <col><data></data></col>     <col><data>471</data></col>     <col><data>635</data></col> </row> <row modid="6" recordid="6" >     <col><data>5428010618040144225</data></col>     <col><data>1261</data></col>     <col><data>0</data></col>     <col><data>321</data></col>     <col><data></data></col>     <col><data>442</data></col>     <col><data>1228</data></col> </row> 

there of course more columns that, omitted simplicity. output example has "fields" of character id, item id, vehicle id, fire count, hit count, play time, score in respective order.

this copy of xsl file using import hit count

<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/xsl/transform" xmlns="http://tempuri.org/test.xsd">         <xsl:template match="/character_list">                 <fmpxmlresult xmlns="http://www.filemaker.com/fmpxmlresult">                         <errorcode>0</errorcode>                         <product build="" name="soe api xml import" version="2.0" />                         <database dateformat="yyyy.mm.dd" layout="" name="" records="" timeformat="k:mm:ss" />                         <metadata>                                 <field emptyok="no" maxrepeat="1" name="character_id" type="text" />                                 <field emptyok="no" maxrepeat="1" name="item_id" type="text" />                                 <field emptyok="no" maxrepeat="1" name="vehicle_id" type="text" />                                 <field emptyok="no" maxrepeat="1" name="value" type="text" />                         </metadata>                         <resultset>                         <xsl:for-each select="character" >                                 <xsl:variable name="cid">                                         <xsl:value-of select="@id" />                                 </xsl:variable>                                 <xsl:for-each select="stats/weapon_stat_list/weapon_stat[@stat_name='weapon_hit_count']" >                                         <xsl:variable name="c">                                                 <xsl:value-of select="position()" />                                         </xsl:variable>                                         <row modid="$c" recordid="$c" >                                                 <col><data><xsl:value-of select="$cid" /></data></col>                                                 <col><data><xsl:value-of select="@item_id" /></data></col>                                                 <col><data><xsl:value-of select="@vehicle_id" /></data></col>                                                 <col><data><xsl:value-of select="@value" /></data></col>                                         </row>                                 </xsl:for-each>                         </xsl:for-each>                         </resultset>                 </fmpxmlresult>     </xsl:template> </xsl:stylesheet> 

in reading around seems might need use for-each-group group info together. i'll continue trying , figure out need make work, i'm hoping can save me time. don't know version of xlst filemaker 12 supports. i'll keep updated if find out more.

if can iterate on keys of stat entries (from comments, keys item_id+vehicle_id values pairs), should pretty easy aggregate attributes belong each key in 1 go.

from expected output, appears every key, there 1 <weapon_stat> element stat_name='weapon_fire_count'. given correct, you'll item_id values iterating on these <weapon_stat> elements:

<xsl:for-each select="stats/weapon_stat_list/weapon_stat[@stat_name='weapon_fire_count']" > 

from these elements, straightforward generate output fields item id, vehicle id, , fire count. other 3 fields, can select value appropriate sibling following (a bit more advanced) xpath expression:

<xsl:value-of select="../weapon_stat[@stat_name='weapon_hit_count' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /> 

the trick compare @item_id , @vehicle_id of respective context nodes (these siblings: ../weapon_stat) @item_id , @vehicle_id of current node (which node selected for-each above).

all in all, want replace inner for-each of xsl file this:

<xsl:for-each select="stats/weapon_stat_list/weapon_stat[@stat_name='weapon_fire_count']" >     <row>         <col><data><xsl:value-of select="$cid" /></data></col>         <col><data><xsl:value-of select="@item_id" /></data></col>         <col><data><xsl:value-of select="@vehicle_id" /></data></col>         <col><data><xsl:value-of select="@value" /></data></col>         <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_hit_count' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>         <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_play_time' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>         <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_score' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>     </row> </xsl:for-each> 

note: omitted modid , recordid counters because there no efficient solution add them in transformation. however, straightforward take result of transformation , add counters through second xsl transformation.

---

update: in pastebin example gave in comment, seems not every key has weapon_fire_count stat. instead of iterating on these stat entries, need iterate on stats, , omit output if there preceding sibling same key:

<xsl:for-each select="stats/weapon_stat_list/weapon_stat" >     <xsl:if test="not(boolean(preceding-sibling::weapon_stat[@item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]))">         <row >             <col><data><xsl:value-of select="$cid" /></data></col>             <col><data><xsl:value-of select="@item_id" /></data></col>             <col><data><xsl:value-of select="@vehicle_id" /></data></col>             <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_fire_count' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>             <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_hit_count' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>             <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_play_time' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>             <col><data><xsl:value-of select="../weapon_stat[@stat_name='weapon_score' , @item_id=current()/@item_id , @vehicle_id=current()/@vehicle_id]/@value" /></data></col>         </row>     </xsl:if> </xsl:for-each>