I have a source xml like below. Im trying to convert to a desired format using xslt as shown below
**Sample XML**
<table>
<row>
<tablename>table1</tablename>
<columnname>col1</columnname>
<columnDesc>col1_desc</columnDesc>
<columnDataType>Number</columnDataType>
<ultimateSourceTable>sourceTable1</ultimateSourceTable>
</row>
<row>
<tablename>table1</tablename>
<columnname>col1</columnname>
<columnDesc>col1_desc</columnDesc>
<columnDataType>Number</columnDataType>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</row>
<row>
<tablename>table2</tablename>
<columnname>table2_col1</columnname>
<columnDesc>table2_col1_desc</columnDesc>
<columnDataType>String</columnDataType>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</row>
</table>
current output
<Prod>
<dataBase>
<physicalTableName>
<tableName>table1</tableName>
</physicalTableName>
<columnList>
<name>col1</name>
<name>col1</name>
<name>table2_col1</name>
</columnList>
<finalSourceList>
<column>
<columnName>col1</columnName>
<ultimateSourceTable>sourceTable1</ultimateSourceTable>
</column>
<column>
<columnName>col1</columnName>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</column>
</finalSourceList>
<physicalTableName>
<tableName>table2</tableName>
</physicalTableName>
<columnList>
<name>col1</name>
<name>col1</name>
<name>table2_col1</name>
</columnList>
<finalSourceList>
<column>
<columnName>table2_col1</columnName>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</column>
</finalSourceList>
</dataBase>
Desired output:
<Prod>
<dataBase>
<physicalTableName>
<tableName>table1</tableName>
</physicalTableName>
<columnList>
<name>col1</name>
<columnDesc>col1_desc</columnDesc>
<columnDataType>Number</columnDataType>
</columnList>
<finalSourceList>
<column>
<columnName>col1</columnName>
<ultimateSourceTable>sourceTable1</ultimateSourceTable>
</column>
<column>
<columnName>col1</columnName>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</column>
</finalSourceList>
<physicalTableName>
<tableName>table2</tableName>
</physicalTableName>
<columnList>
<name>table2_col1</name>
<columnDesc>table2_col1_desc</columnDesc>
<columnDataType>String</columnDataType>
</columnList>
<finalSourceList>
<column>
<columnName>table2_col1</columnName>
<ultimateSourceTable>sourceTable2</ultimateSourceTable>
</column>
</finalSourceList>
</dataBase>
</Prod>
XSLT:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8"
indent="yes" />
<xsl:template match="/">
<Prod>
<xsl:apply-templates />
</Prod>
</xsl:template>
<xsl:key name="kElsByGroup" match="row" use="tablename" />
<xsl:key name="TableColByGroup" match="row" use="concat(tablename,'|',columnname)" />
<xsl:template match="row">
<xsl:apply-templates />
</xsl:template>
<xsl:template
match="row[generate-id()=generate-id(key('kElsByGroup',tablename)[1])]">
<dataBase>
<physicalTableName>
<tableName>
<xsl:value-of select="tablename"></xsl:value-of>
</tableName>
</physicalTableName>
<columnList>
<xsl:for-each
select="//row[generate-id()=generate-id(key('TableColByGroup',concat(tablename,'|',columnname))[1])]">
<xsl:element name="column">
<name>
<xsl:value-of
select="columnname"></xsl:value-of>
</name>
</xsl:element>
</xsl:for-each>
</columnList>
<finalSourceList>
<xsl:for-each
select="key('kElsByGroup',tablename)">
<xsl:element name="column">
<columnName>
<xsl:value-of
select="columnname"></xsl:value-of>
</columnName>
<sourceTable>
<xsl:value-of
select="ultimateSourceTable"></xsl:value-of>
</sourceTable>
</xsl:element>
</xsl:for-each>
</finalSourceList>
</dataBase>
</xsl:template>
<xsl:template
match="row[not(generate-id()=generate-id(key('kElsByGroup',tablename)[1]))]" />
</xsl:stylesheet>
So, I basically want to have only unique value in my columnList tag. I'm trying to subgroup using muenchian grouping but, I still 2 entries of col1 in my columnList tag. Can someone help me out?
With a single table element in the source containing rows of different tables it seems you can solve it using modes to process rows twice, once to create the data of a result table and the second time to create the details of each result table:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="xml" indent="yes"/>
<xsl:key name="table" match="row" use="tablename"/>
<xsl:key name="col" match="row" use="concat(tablename, '|', columnname)"/>
<xsl:template match="table">
<Prod>
<dataBase>
<xsl:apply-templates select="row[generate-id() = generate-id(key('table', tablename)[1])]" mode="table"/>
</dataBase>
</Prod>
</xsl:template>
<xsl:template match="row" mode="table">
<physicalTableName>
<xsl:value-of select="tablename"/>
</physicalTableName>
<columnList>
<xsl:apply-templates select="key('table', tablename)[generate-id() = generate-id(key('col', concat(tablename, '|', columnname))[1])]/columnname"/>
</columnList>
<finalSourceList>
<xsl:apply-templates select="key('table', tablename)"/>
</finalSourceList>
</xsl:template>
<xsl:template match="row/columnname">
<name>
<xsl:value-of select="."/>
</name>
</xsl:template>
<xsl:template match="row">
<column>
<xsl:apply-templates select="*[not(self::tablename)]" mode="source-list"/>
</column>
</xsl:template>
<xsl:template match="row/columnname" mode="source-list">
<columnName>
<xsl:value-of select="."/>
</columnName>
</xsl:template>
<xsl:template match="row/ultimateSourceTable" mode="source-list">
<xsl:copy-of select="."/>
</xsl:template>
</xsl:stylesheet>