I have flat XML data that I want to transform, including sorting by the date value and adding an incrementing row number.
I have the following XML:
<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Fred</FirstName>
<UMR>A</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Theo</FirstName>
<UMR>A</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Laura</FirstName>
<UMR>A</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Sarah</FirstName>
<UMR>B</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Gary</FirstName>
<UMR>B</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Bob</FirstName>
<UMR>B</UMR>
</_x0037_906aa9f-c0f8-4909-baa9-ba3090d6c6e5>
</NewDataSet>
Which I need to transform into the following format:
<Instances>
<Instance>
<formdata>
<data>
<UMR>A</UMR>
<Table TableId="Details">
<TableRow RowNumber="">
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Fred</FirstName>
</TableRow>
<TableRow RowNumber="">
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Theo</FirstName>
</TableRow>
<TableRow RowNumber="">
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Laura</FirstName>
</TableRow>
</Table>
</data>
</formdata>
</Instance>
<Instance>
<formdata>
<data>
<UMR>B</UMR>
<Table TableId="Details">
<TableRow RowNumber="">
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Sarah</FirstName>
</TableRow>
<TableRow RowNumber="">
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Gary</FirstName>
</TableRow>
<TableRow RowNumber="">
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Bob</FirstName>
</TableRow>
</Table>
</data>
</formdata>
</Instance>
</Instances>
I have the following XSLT Style Sheet:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<!-- set up the key. -->
<xsl:key name="UMRItems" match="NewDataSet/*" use="UMR"/>
<!-- Matches the whole dataset and creates a copy-->
<xsl:template match="@* | node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
<!-- Matches NewDataSet element and replaces with Instances element -->
<xsl:template match="NewDataSet">
<Instances>
<xsl:apply-templates select="*[generate-id(.)=generate-id(key('UMRItems', UMR)[1])]"/>
</Instances>
</xsl:template>
<!-- Matches the GUID element and replaces with Instance element -->
<xsl:template match="NewDataSet/*">
<Instance>
<formdata>
<data>
<!-- Selects non-table form data -->
<UMR><xsl:value-of select="UMR"/></UMR>
<Table>
<xsl:attribute name="TableId">
<xsl:text>Details</xsl:text>
</xsl:attribute>
<xsl:for-each select="key('UMRItems', UMR)">
<xsl:sort select="InceptionDate"/>
<xsl:call-template name="TableDetails"></xsl:call-template>
</xsl:for-each>
</Table>
</data>
</formdata>
</Instance>
</xsl:template>
<xsl:template name="TableDetails">
<TableRow RowNumber="" >
<InceptionDate><xsl:value-of select="InceptionDate"/></InceptionDate>
<FirstName><xsl:value-of select="FirstName"/></FirstName>
</TableRow>
</xsl:template>
</xsl:stylesheet>
But I expect the following output:
<Instances>
<Instance>
<formdata>
<data>
<UMR>A</UMR>
<Table TableId="Details">
<TableRow RowNumber="1">
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Fred</FirstName>
</TableRow>
<TableRow RowNumber="2">
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Theo</FirstName>
</TableRow>
<TableRow RowNumber="3">
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Laura</FirstName>
</TableRow>
</Table>
</data>
</formdata>
</Instance>
<Instance>
<formdata>
<data>
<UMR>B</UMR>
<Table TableId="Details">
<TableRow RowNumber="1">
<InceptionDate>01/01/2021</InceptionDate>
<FirstName>Sarah</FirstName>
</TableRow>
<TableRow RowNumber="2">
<InceptionDate>02/02/2021</InceptionDate>
<FirstName>Gary</FirstName>
</TableRow>
<TableRow RowNumber="3">
<InceptionDate>03/03/2021</InceptionDate>
<FirstName>Bob</FirstName>
</TableRow>
</Table>
</data>
</formdata>
</Instance>
</Instances>
However I am unable to generate the RowNumbers which should increment per Table.
I have tried using:
Position()
but this results in all RowNumbers = 1
count(preceding::UMR[(preceding::UMR= .)]) 1
This seems closer but only increments rows 1 and 2.
Thanks
CodePudding user response:
Why don't you do simply:
XSLT 1.0
<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:key name="UMRItems" match="/NewDataSet/*" use="UMR"/>
<xsl:template match="/NewDataSet">
<Instances>
<!-- a group for each distinct UMR value -->
<xsl:for-each select="*[generate-id(.)=generate-id(key('UMRItems', UMR)[1])]">
<Instance>
<formdata>
<data>
<xsl:copy-of select="UMR"/>
<Table TableId="Details">
<!-- a row for each member of current group -->
<xsl:for-each select="key('UMRItems', UMR)">
<TableRow RowNumber="{position()}">
<xsl:copy-of select="InceptionDate | FirstName"/>
</TableRow>
</xsl:for-each>
</Table>
</data>
</formdata>
</Instance>
</xsl:for-each>
</Instances>
</xsl:template>
</xsl:stylesheet>