Thank you for taking the time to look my question.
I have an input structure (below) where each row represents a movement from one location item to another. So each row has a from location and a to location.
The target is a nested xml where a location object has some header and item level information. I need to pick distint from and to locations across all the rows and map their item level details.
Example of the input and the required transformed output.
I have a flat database extract xml that looks something like
<Data>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID001</COMP_ID_F>
<ITEM_ID_F>1</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID001</COMP_ID_T>
<ITEM_ID_T>2</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID001</COMP_ID_F>
<ITEM_ID_F>1</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID001</COMP_ID_T>
<ITEM_ID_T>2</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID001</COMP_ID_F>
<ITEM_ID_F>1</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID001</COMP_ID_T>
<ITEM_ID_T>3</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID001</COMP_ID_F>
<ITEM_ID_F>2</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID001</COMP_ID_T>
<ITEM_ID_T>4</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID002</COMP_ID_F>
<ITEM_ID_F>1</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID001</COMP_ID_T>
<ITEM_ID_T>5</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
<row>
<Movement>Home to Work</Movement>
<COMP_ID_F>HID002</COMP_ID_F>
<ITEM_ID_F>2</ITEM_ID_F>
<FIELDS_F> More home related data </FIELDS_F>
<COMP_ID_T>WID002</COMP_ID_T>
<ITEM_ID_T>1</ITEM_ID_T>
<FIELDS_T> More work related data</FIELDS_T>
</row>
</Data>
The transformed output needs to be
<Target>
<Events>
<Movement>Home to Work</Movement>
<Movement>Home to Work</Movement>
<Movement>Home to Work</Movement>
<Movement>Home to Work</Movement>
<Movement>Home to Work</Movement>
<Movement>Home to Work</Movement>
</Events>
<Locations>
<Home>
<id>HID001</id>
<item>
<id>1</id>
</item>
<item>
<id>2</id>
</item>
</Home>
<Home>
<id>HID002</id>
<item>
<id>1</id>
</item>
<item>
<id>2</id>
</item>
</Home>
<Work>
<id>WID001</id>
<item>
<id>1</id>
</item>
<item>
<id>2</id>
</item>
<item>
<id>3</id>
</item>
<item>
<id>4</id>
</item>
<item>
<id>5</id>
</item>
</Work>
<Work>
<id>WID002</id>
<item>
<id>1</id>
</item>
</Work>
</Locations>
</Target>
Now I've tried a number of options using Keys and generate id's. So please have a look and point me at where I'm going wrong. Or if there is a better way of achieving this output.
I've created 4 keys
<xsl:key name="fcreg" match="row" use="COMP_ID_F"/>
<xsl:key name="fcireg" match="row" use="ITEM_ID_F"/>
<xsl:key name="tcreg" match="row" use="COMP_ID_T"/>
<xsl:key name="tcireg" match="row" use="ITEM_ID_T"/>
My XSLT does the following logic
<Target>
<!--transform all events-->
<Events>
<xsl:for-each select="Data/row">
<!-- get movement-->
</xsl:for-each>
</Events>
<!--transform all from locations-->
<xsl:for-each select="Data/row[generate-id() = generate-id(key('fcreg',COMP_ID_F)[1])]">
<xsl:for-each select="key('fcreg',COMP_ID_F)[generate-id() = generate-id(key('fcireg', ITEM_ID_F)[COMP_ID_F=current()/COMP_ID_F][1])]">
<xsl:variable name="items" select="key('fcreg', COMP_ID_F)[ITEM_ID_F = current()/ITEM_ID_F]"/>
<home>
<xsl:value-of select="COMP_ID_F"/>
<xsl:for-each select="$items">
<item>
<id>
<xsl:value-of select="ITEM_ID_F"/>
</id>
</item>
</xsl:for-each>
</home>
</xsl:for-each>
</xsl:for-each>
<!--transform all to locations-->
<xsl:for-each select="Data/row[generate-id() = generate-id(key('tcreg',COMP_ID_T)[1])]">
<xsl:for-each select="key('tcreg',COMP_ID_T)[generate-id() = generate-id(key('tcireg', ITEM_ID_T)[COMP_ID_T=current()/COMP_ID_T][1])]">
<xsl:variable name="items" select="key('tcreg', COMP_ID_T)[ITEM_ID_T = current()/ITEM_ID_T]"/>
<work>
<xsl:value-of select="COMP_ID_T"/>
<xsl:for-each select="$items">
<item>
<id>
<xsl:value-of select="ITEM_ID_T"/>
</id>
</item>
</xsl:for-each>
</work>
</xsl:for-each>
</xsl:for-each>
</Target>
Where am I going wrong? Any help is greatly appreciated.
Thanks in advance.
It looks like you are trying to get the distinct COMP_ID_F elements, and for each distinct value, you then want to list all the distinct ITEM_ID_F associated with it.
Your fcreg key is fine, but because the ITEM_ID_F items depend on the value of COMP_ID_F, you need a concatenated key for fcireg
<xsl:key name="fcireg" match="row" use="concat(COMP_ID_F, '|', ITEM_ID_F)"/>
So, you start off by selecting ITEM_ID_F as before...
<xsl:for-each select="Data/row[generate-id() = generate-id(key('fcreg',COMP_ID_F)[1])]">
But within this, you then select the distinct ITEM_ID_F items for the current COMP_ID_F element. This is done like this:
<xsl:for-each select="Data/row[generate-id() = generate-id(key('fcreg',COMP_ID_F)[1])]">
<home>
<id><xsl:value-of select="COMP_ID_F" /></id>
<xsl:for-each select="key('fcreg',COMP_ID_F)[generate-id() = generate-id(key('fcireg',concat(COMP_ID_F, '|', ITEM_ID_F))[1])]">
<item>
<id>
<xsl:value-of select="ITEM_ID_F"/>
</id>
</item>
</xsl:for-each>
</home>
</xsl:for-each>
You would have a similar one for the "T" elements
Try this XSLT
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" encoding="UTF-8" indent="yes" />
<xsl:key name="fcreg" match="row" use="COMP_ID_F"/>
<xsl:key name="fcireg" match="row" use="concat(COMP_ID_F, '|', ITEM_ID_F)"/>
<xsl:key name="tcreg" match="row" use="COMP_ID_T"/>
<xsl:key name="tcireg" match="row" use="concat(COMP_ID_T, '|', ITEM_ID_T)"/>
<xsl:template match="/">
<Target>
<Events>
<xsl:copy-of select="Data/row/Movement" />
</Events>
<xsl:for-each select="Data/row[generate-id() = generate-id(key('fcreg',COMP_ID_F)[1])]">
<Home>
<id><xsl:value-of select="COMP_ID_F" /></id>
<xsl:for-each select="key('fcreg',COMP_ID_F)[generate-id() = generate-id(key('fcireg',concat(COMP_ID_F, '|', ITEM_ID_F))[1])]">
<item>
<id>
<xsl:value-of select="ITEM_ID_F"/>
</id>
</item>
</xsl:for-each>
</Home>
</xsl:for-each>
<xsl:for-each select="Data/row[generate-id() = generate-id(key('tcreg',COMP_ID_T)[1])]">
<Work>
<id><xsl:value-of select="COMP_ID_T" /></id>
<xsl:for-each select="key('tcreg',COMP_ID_T)[generate-id() = generate-id(key('tcireg',concat(COMP_ID_T, '|', ITEM_ID_T))[1])]">
<item>
<id>
<xsl:value-of select="ITEM_ID_T"/>
</id>
</item>
</xsl:for-each>
</Work>
</xsl:for-each>
</Target>
</xsl:template>
</xsl:stylesheet>