By Antony Reynolds-Oracle on Oct 19, 2011
Adding Structure to Flat XML Documents
A friend recently was wondering how to convert a flat document structure to a more structured form.
The type of flat structure is shown in the diagram below:
The deptNo and deptName fields repeat for each employee in the department.
This would be better represented as a structured format like the one shown below:
Note that the department details are now represented once per department and employees appear in a sequence called emp. This is a more natural representation and easier to manipulate elsewhere.
So the question is, how do I get from the flat schema to the structured schema?
To get just the first time a department appears we select all the entries that do not have the same deptNo earlier in the document using this XPath expression:
<xsl:for-each select="/ns1:collection/ns1:entry[not(ns1:deptNo = preceding-sibling::ns1:entry/ns1:deptNo)]">
Within the first occurrence of a department we then set a variable to hold the department number:
<xsl:variable name="DeptNo" select="ns1:deptNo"/>
Within the department we then put in the employee included in the current node. We then select all the other entries that have the same department number and add their employee details by using the following XPath expression:
<xsl:for-each select="following-sibling::ns1:entry[ns1:deptNo = $DeptNo]">
A sample JDeveloper project to test this is available here.