Grouping an XML document based on element names

It has been a while since we have talked about some "pure XML" problem to be solved with XQuery; so when I read this un-answered post on the Stylus Studio Developer Network I thought that was a good chance to talk about it here as an interesting XQuery example.
The problem involves moving from a flat XML structure like this one: <Fields> <br /> <MAIN1_SUB1_COLNAME1>1</MAIN1_SUB1_COLNAME1><br /> <MAIN1_SUB1_COLNAME2>2</MAIN1_SUB1_COLNAME2><br /> <MAIN1_SUB1_COLNAME3>3</MAIN1_SUB1_COLNAME3><br /> <MAIN1_SUB2_COLNAME1>4</MAIN1_SUB2_COLNAME1><br /> <MAIN1_SUB2_COLNAME2>5</MAIN1_SUB2_COLNAME2><br /> <MAIN1_SUB2_COLNAME3>6</MAIN1_SUB2_COLNAME3><br /> <MAIN2_SUB1_COLNAME1>7</MAIN2_SUB1_COLNAME1><br /> <MAIN2_SUB1_COLNAME2>8</MAIN2_SUB1_COLNAME2><br /> <MAIN2_SUB1_COLNAME3>9</MAIN2_SUB1_COLNAME3><br /></Fields>...to a more hierarchical XML that "explodes" the implicit structure hidden in the original XML element names: <fields><br /> <Main1><br /> <Sub><br /> <colname1>1</colname1><br /> <colname2>2</colname2><br /> <colname3>3</colname3><br /> </Sub><br /> ...<br /> </Main1><br /> ...<br /></fields>In the end this is a grouping problem, but a bit trickier than usual, as it involves recognizing and exploding the groups from the original XML element names.
Even if XQuery 1.0 doesn't support grouping explicitly, the fn:distinct-values() function is extremely useful in solving grouping problems. fn:distinct-values() gets a sequence of atomic values in input and returns a sequence containing the same values with any duplicate removed. That helps a lot with our problem, as we can retrieve what all the unique top level categories are (MAINx) and what the unique sub categories are (SUBy) for each top level one. Add to that a very simple use of the fn:tokenize() function that splits a name like "MAIN1_SUB1_COLNAME1" into a sequence like ("MAIN1", "SUB1", "COLNAME1"), and the problem is easily solved; here is the XQuery I came up with:declare option ddtek:serialize "indent=yes";<br /><br />declare function local:splitName($node as element()) as xs:string* {<br /> tokenize($node/local-name(), "_")<br />};<br /><br />let $input :=<br /> <Fields> <br /> <MAIN1_SUB1_COLNAME1>1</MAIN1_SUB1_COLNAME1><br /> <MAIN1_SUB1_COLNAME2>2</MAIN1_SUB1_COLNAME2><br /> <MAIN1_SUB1_COLNAME3>3</MAIN1_SUB1_COLNAME3><br /> <MAIN1_SUB2_COLNAME1>4</MAIN1_SUB2_COLNAME1><br /> <MAIN1_SUB2_COLNAME2>5</MAIN1_SUB2_COLNAME2><br /> <MAIN1_SUB2_COLNAME3>6</MAIN1_SUB2_COLNAME3><br /> <MAIN2_SUB1_COLNAME1>7</MAIN2_SUB1_COLNAME1><br /> <MAIN2_SUB1_COLNAME2>8</MAIN2_SUB1_COLNAME2><br /> <MAIN2_SUB1_COLNAME3>9</MAIN2_SUB1_COLNAME3><br /> </Fields><br /><br />return<br /> <fields> {<br /> for $mainlevel-prefix in distinct-values($input/*/local:splitName(.)[1])<br /> return<br /> element {$mainlevel-prefix} {<br /> for $sublevel-prefix in distinct-values($input/*[starts-with(local-name(), concat($mainlevel-prefix, "_"))]/local:splitName(.)[2])<br /> return<br /> <Sub> {<br /> for $sublevel-node in $input/*[starts-with(local-name(), concat($mainlevel-prefix,"_",$sublevel-prefix,"_"))]<br /> return<br /> element {local:splitName($sublevel-node)[3]} {$sublevel-node/text()}<br /> } </Sub><br /> }<br /> } </fields>That generates the following XML result, which is what we are looking for:<fields><br /> <MAIN1><br /> <Sub><br /> <COLNAME1>1</COLNAME1><br /> <COLNAME2>2</COLNAME2><br /> <COLNAME3>3</COLNAME3><br /> </Sub><br /> <Sub><br /> <COLNAME1>4</COLNAME1><br /> <COLNAME2>5</COLNAME2><br /> <COLNAME3>6</COLNAME3><br /> </Sub><br /> </MAIN1><br /> <MAIN2><br /> <Sub><br /> <COLNAME1>7</COLNAME1><br /> <COLNAME2>8</COLNAME2><br /> <COLNAME3>9</COLNAME3><br /> </Sub><br /> </MAIN2><br /></fields><br />A simple example, but it makes use of some useful functions and structures in XQuery; just to remind us that while we keep talking about how useful XQuery is to deal with heterogeneous data sources and leverage the XML Data Model as an abstraction from the physical details of the data we are dealing with, XQuery is extremely powerful and flexible also in the "simpler" cases where you need to manipulate and re-arrange XML structures.