More on an XQuery format-number function

Recently Chris Wallace has been writing about formatting numbers in XQuery; as you may remember, I did blog about an XQuery-based format-number function some time ago, providing a partial implementation.

Chris mentions my post in his Wikibooks entry, and he has been so nice to run some tests and identify a few bugs and limitations. As I've recently had to "enjoy" a red eye flight on my way back to Boston, I couldn't resist the temptation to fix some of those issues.

Attached is a better version; it includes also support for the typical functionality you would access through xsl:decimal-format. The XQuery includes the tests that Chris posted on the Wikibooks entry, plus a few more, whose results are:<tests><br /> <test><br /> <code>local:format-number(12345678.9, '#,###.00')</code><br /> <xquery-result>12,345,678.90</xquery-result><br /> <xslt-result>12,345,678.90</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(-12345678.9, '#,###.00')</code><br /> <xquery-result>-12,345,678.90</xquery-result><br /> <xslt-result>-12,345,678.90</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(12345.67,'00000000.00')</code><br /> <xquery-result>00012345.67</xquery-result><br /> <xslt-result>00012345.67</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(12345.67,'0,000.0000;-000,000.00')</code><br /> <xquery-result>12,345.6700</xquery-result><br /> <xslt-result>12,345.6700</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(-12345.67,'0,000.0000;-000,000.00')</code><br /> <xquery-result>-012,345.67</xquery-result><br /> <xslt-result>-012,345.67</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(12345.67,',000')</code><br /> <xquery-result>12,346</xquery-result><br /> <xslt-result>12,346</xslt-result><br /> </test><br /> <test><br /> <code>local:format-number(12345.67,'$,000')</code><br /> <xquery-result>$12,346</xquery-result><br /> <xslt-result>$12,346</xslt-result><br /> </test><br /></tests>
This is far from being a complete solution; there are several patterns that are not supported at all; but it's getting better... As I commented here I do believe that format-number needs to become part of the XQuery language and natively supported by the engine, which is the direction where XQuery 1.1 is moving.

formatnumber-xquery.xq

XQuery for the SQL programmer – And performance?

In this last post of the XQuery for the SQL programmer series, I would like to spend a few minutes on performance. The previous post listed a dozen data integration uses cases, deploying an XQuery engine on top of your SQL database. The question is of course how performant such solution can be.

If you have a rather naive implementation retrieving the complete table (or multiple tables) and subsequently perform queries on an in-memory representation, well of course, performance will be unacceptable slow. If it works at all, once you start to query your production database with millions of records.

The tricky part is to have a performant and scalable XQuery engine, that is capable of translating XQuery straight into SQL. And we believe DataDirect XQuery is...

We wrote a white paper about translating XQuery to SQL, showing concrete XQuery queries and the corresponding SQL. I would advice to read the document, but in short, the SQL generation is based on the following principles

• Minimize data retrieval
• Leverage the database strengths
• Optimize for each database
• Retrieve data efficiently
• Support incremental evaluation
• Optimize for XML hierarchies
• Give the programmer the last word

And of course, when it comes to answering your data integration challenges, it's a matter of joining and aggregating relational data with other formats in the most optimal way. We have blogged about this topic before, but there is of course much more to say. Looks like I should spend some more blog-time on the performance and scalability aspects of data integration through XQuery.

And remember, performance is one aspect, developer's productivity is also important. Think of all the APIs to master, Java code to write - and maintain! - to combine multiple data sources, while all this can be done in a single XQuery.

Tech Tags: XQuery SQL

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.