All,
I'm an XSL Newbie again (last used it about 2 years ago). Anyway, I'm looking
for some review/feedback on a relatively simple stylesheet. As background, I'm
executing an SQL query against an Oracle database, using Oracle's CONNECT BY
functionality to bring back a hierarchical result set. I'm then transforming
the results into XML via Oracle 9.1's SYS_XMLAGG and SYS_XMLGEN functionality
(largely unimportant, but I mention it in case it helps anyone), which brings
me back a 'flat' XML representation of the previously-hierarchical result set.
(This version of Oracle isn't smart enough to maintain the hierarchical nature
of the data through the XML conversion - newer versions are, but this is the
version I have to use...) So, I end up w/ XML elements that are in the proper
'order', but not nested. The XSL stylesheet then nests things appropriately
based on ROWID values (unique id's for eac
h row in the resultset) for each node and its parent in the hierarchical
relationship, as!
well as a LEVEL attribute returned from the query. (XML, XSL and output are
included below).
Anyway, I'd like to know if/how I can do this in a smarter manner, if I've made
any serious mis-steps or such, if this approach is likely to bring the machine
to its knees for large datasets, etc. (this will be occurring on the database
server).
Other specific questions:
- thoughts on having two templates for the nodes to separate out top-level
nodes, or if one template w/ an xsl:if would be better
- the prevailing philosophy as regards whether to have an empty CHILDREN
element (see below) if there are no children or not
- I've tried to make this as generic as possible, as I have opportunity to
apply it to numerous situations, but have been unable to find a way to
parameterize the ROWSET_, ROWID_ and PARENTROWID_ tag names (ie, to allow the
user to set these to another value in their database query and pass parameters
to the stylesheet naming their substituted values) - if anyone has any thoughts
on this, I'd be very interested to hear them, as well. (It works as-is, and is
an acceptable work-around, but I'd at least like to know if its possible.)
All advice is appreciated! (and yes, lets establish up-front, I'm an idiot...
That's why I'm here... <:-)
Thanks Very Much!!
Jim Stoll
<<input XML>>
<?xml version="1.0"?>
<ROWSET>
<NODE>
<LEVEL_>1</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAG</ROWID_>
<SRCD>1</SRCD>
<ELEMENT1>1e1</ELEMENT1>
<ELEMENT2>1e2</ELEMENT2>
<ELEMENT3>1e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>1</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAH</ROWID_>
<SRCD>2</SRCD>
<ELEMENT1>2e1</ELEMENT1>
<ELEMENT2>2e2</ELEMENT2>
<ELEMENT3>2e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>2</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAI</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAH</PARENTROWID_>
<SRCD>2,1</SRCD>
<ELEMENT1>21e1</ELEMENT1>
<ELEMENT2>21e2</ELEMENT2>
<ELEMENT3>21e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>3</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAJ</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAI</PARENTROWID_>
<SRCD>2,1,1</SRCD>
<ELEMENT1>211e1</ELEMENT1>
<ELEMENT2>211e2</ELEMENT2>
<ELEMENT3>211e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>3</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAK</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAI</PARENTROWID_>
<SRCD>2,1,2</SRCD>
<ELEMENT1>212e1</ELEMENT1>
<ELEMENT2>212e2</ELEMENT2>
<ELEMENT3>212e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>1</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAL</ROWID_>
<SRCD>3</SRCD>
<ELEMENT1>3e1</ELEMENT1>
<ELEMENT2>3e2</ELEMENT2>
<ELEMENT3>3e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>2</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAM</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAL</PARENTROWID_>
<SRCD>3,1</SRCD>
<ELEMENT1>31e1</ELEMENT1>
<ELEMENT2>31e2</ELEMENT2>
<ELEMENT3>31e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>2</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAN</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAL</PARENTROWID_>
<SRCD>3,2</SRCD>
<ELEMENT1>32e1</ELEMENT1>
<ELEMENT2>32e2</ELEMENT2>
<ELEMENT3>32e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>3</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAO</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAN</PARENTROWID_>
<SRCD>3,2,1</SRCD>
<ELEMENT1>321e1</ELEMENT1>
<ELEMENT2>321e2</ELEMENT2>
<ELEMENT3>321e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>2</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAP</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAL</PARENTROWID_>
<SRCD>3,3</SRCD>
<ELEMENT1>33e1</ELEMENT1>
<ELEMENT2>33e2</ELEMENT2>
<ELEMENT3>33e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>3</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAQ</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAP</PARENTROWID_>
<SRCD>3,3,1</SRCD>
<ELEMENT1>331e1</ELEMENT1>
<ELEMENT2>331e2</ELEMENT2>
<ELEMENT3>331e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>4</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAR</ROWID_>
<PARENTROWID_>AAAMDnAAPAAAEHuAAQ</PARENTROWID_>
<SRCD>3,3,1,1</SRCD>
<ELEMENT1>3311e1</ELEMENT1>
<ELEMENT2>3311e2</ELEMENT2>
<ELEMENT3>3311e3</ELEMENT3>
</NODE>
<NODE>
<LEVEL_>1</LEVEL_>
<ROWID_>AAAMDnAAPAAAEHuAAS</ROWID_>
<SRCD>4</SRCD>
<ELEMENT1>4e1</ELEMENT1>
<ELEMENT2>4e2</ELEMENT2>
<ELEMENT3>4e3</ELEMENT3>
</NODE>
</ROWSET>
<<XSL>>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
version="1.0">
<!--
Preconditions on the database-generated XML processed by this
stylesheet:
- there will always be a root element named ROWSET,
that is not part of the 'data' hierarchy (ie, its part of the XML hierarchy,
but not part of the original relational data hierarchy)
- all 'data' elements will be siblings underneath the
root element (result of the 'flattening' nature of the database's
result-set-to-XML conversion)
- every 'data' element will have a child 'utility'
element named LEVEL_ that indicates the nesting level in the original data
hierarchy, where 1 is a top-level element (ie, no parent in the data hierarchy)
- every 'data' element will have a child 'utility'
element named ROWID_ that is a unique identifier for the row in the original
data hierarchy
- every data element that is a child of another data
element will have a child 'utility' element named PARENTROWID_ that identifies
the data element's parent
-->
<!--while database-generated XML will always be contained in a root
node named ROWSET, the user can choose whether to include a root tag in the
output, and if so, what that tag will be named - defaults to ROWSET-->
<xsl:param name="root-tag-param">ROWSET</xsl:param>
<!--if a node has children, they will be grouped in a wrapper tag, but
the user can specify the wrapper tag name if desired - defaults to CHILDREN-->
<xsl:param name="children-tag-param">CHILDREN</xsl:param>
<!--by default, the mandatory LEVEL_ and ROWID_ tags provided in the
database-generated XML (and required for this stylesheet's operations) will not
be included in the output, but can be if desired-->
<xsl:param name="output-level-tag-param">false</xsl:param>
<xsl:param name="output-rowid-tag-param">false</xsl:param>
<xsl:param name="output-parent-rowid-tag-param">false</xsl:param>
<!--recall precondition that the database-generated XML will always
have a root element named ROWSET, and that all 'data' elements will be siblings
under the root, so map all nodes to their parent-->
<xsl:key name="key-nodes-by-parent" match="/ROWSET/*"
use="PARENTROWID_"/>
<xsl:template match="/">
<!--if a root tag value is provided, use it,otherwise, just
produce a fragment-->
<xsl:if test="$root-tag-param!=''">
<xsl:text
disable-output-escaping="yes"><</xsl:text><xsl:value-of
select="$root-tag-param"/><xsl:text
disable-output-escaping="yes">></xsl:text>
</xsl:if>
<!--always want to process the document though,
regardless of whether a root tag is to be output or not!-->
<xsl:apply-templates/>
<xsl:if test="$root-tag-param!=''">
<xsl:text
disable-output-escaping="yes"></</xsl:text><xsl:value-of
select="$root-tag-param"/><xsl:text
disable-output-escaping="yes">></xsl:text>
</xsl:if>
</xsl:template>
<!--all top-level parents will have a level of 1 - only match/process
these, as they will in turn process their children via the key map-->
<xsl:template match="*[LEVEL_='1']">
<xsl:call-template name="duplicate-node">
<xsl:with-param name="duplicatee-param" select="."/>
</xsl:call-template>
</xsl:template>
<!--don't match children(any node w/ a level != 1) - as they'll be
processed by their parent-->
<xsl:template match="*[LEVEL_!='1']">
</xsl:template>
<!--for each node - either top-level parents resulting from a match, or
children resulting from key iteration - duplicate the node, its children and
attributes-->
<xsl:template name="duplicate-node">
<xsl:param name="duplicatee-param"/>
<xsl:copy>
<xsl:copy-of select="@*"/>
<!--output the 'utility' elements if/as specified by
params-->
<xsl:copy-of select="*[(not(self::thisElement)) and
(not(name()='LEVEL_') or (name()='LEVEL_' and $output-level-tag-param='true'))
and (not(name()='ROWID_') or (name()='ROWID_' and
$output-rowid-tag-param='true')) and (not(name()='PARENTROWID_') or
(name()='PARENTROWID_' and $output-parent-rowid-tag-param='true'))]"/>
<xsl:element name="{$children-tag-param}">
<xsl:for-each
select="key('key-nodes-by-parent', ROWID_)">
<xsl:call-template
name="duplicate-node">
<xsl:with-param
name="duplicatee-param" select="."/>
</xsl:call-template>
</xsl:for-each>
</xsl:element>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
<<output XML>>
<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
<NODE>
<SRCD>1</SRCD>
<ELEMENT1>1e1</ELEMENT1>
<ELEMENT2>1e2</ELEMENT2>
<ELEMENT3>1e3</ELEMENT3>
<CHILDREN />
</NODE>
<NODE>
<SRCD>2</SRCD>
<ELEMENT1>2e1</ELEMENT1>
<ELEMENT2>2e2</ELEMENT2>
<ELEMENT3>2e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>2,1</SRCD>
<ELEMENT1>21e1</ELEMENT1>
<ELEMENT2>21e2</ELEMENT2>
<ELEMENT3>21e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>2,1,1</SRCD>
<ELEMENT1>211e1</ELEMENT1>
<ELEMENT2>211e2</ELEMENT2>
<ELEMENT3>211e3</ELEMENT3>
<CHILDREN />
</NODE>
<NODE>
<SRCD>2,1,2</SRCD>
<ELEMENT1>212e1</ELEMENT1>
<ELEMENT2>212e2</ELEMENT2>
<ELEMENT3>212e3</ELEMENT3>
<CHILDREN />
</NODE>
</CHILDREN>
</NODE>
</CHILDREN>
</NODE>
<NODE>
<SRCD>3</SRCD>
<ELEMENT1>3e1</ELEMENT1>
<ELEMENT2>3e2</ELEMENT2>
<ELEMENT3>3e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>3,1</SRCD>
<ELEMENT1>31e1</ELEMENT1>
<ELEMENT2>31e2</ELEMENT2>
<ELEMENT3>31e3</ELEMENT3>
<CHILDREN />
</NODE>
<NODE>
<SRCD>3,2</SRCD>
<ELEMENT1>32e1</ELEMENT1>
<ELEMENT2>32e2</ELEMENT2>
<ELEMENT3>32e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>3,2,1</SRCD>
<ELEMENT1>321e1</ELEMENT1>
<ELEMENT2>321e2</ELEMENT2>
<ELEMENT3>321e3</ELEMENT3>
<CHILDREN />
</NODE>
</CHILDREN>
</NODE>
<NODE>
<SRCD>3,3</SRCD>
<ELEMENT1>33e1</ELEMENT1>
<ELEMENT2>33e2</ELEMENT2>
<ELEMENT3>33e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>3,3,1</SRCD>
<ELEMENT1>331e1</ELEMENT1>
<ELEMENT2>331e2</ELEMENT2>
<ELEMENT3>331e3</ELEMENT3>
<CHILDREN>
<NODE>
<SRCD>3,3,1,1</SRCD>
<ELEMENT1>3311e1</ELEMENT1>
<ELEMENT2>3311e2</ELEMENT2>
<ELEMENT3>3311e3</ELEMENT3>
<CHILDREN />
</NODE>
</CHILDREN>
</NODE>
</CHILDREN>
</NODE>
</CHILDREN>
</NODE>
<NODE>
<SRCD>4</SRCD>
<ELEMENT1>4e1</ELEMENT1>
<ELEMENT2>4e2</ELEMENT2>
<ELEMENT3>4e3</ELEMENT3>
<CHILDREN />
</NODE>
</ROWSET>