New to SQL/XML: Convert traditional result set into XML doc?

[markaddleman]

Suppose I have two tables:

user:

user_id	Name
maddleman	Mark Addleman

phonenumber:

user_id	phone_number
maddleman	123-123-1234
maddleman	456-456-4567

I want to produce the following XML output:

<user>Mark Addleman</user>

   <phone_number>123-123-1234</phone_number>

   <phone_number>456-456-4567</phone_number>

</user>

I can't figure out how to nest the SELECT of the phonenumber table in the outter SELECT in a way that makes Teiid happy.

I'm brand new to SQL/XML so if there is some tutorial website you can point me to, I'd really appreciate it.

[markaddleman]

I just realized that all of the XML functions are scalar so I think I'm out of luck.  Is there no function that takes a query/result set and converts it to an xml doc?

[shawkins]

To produce a single element per user such that there would be one row in the result set for each user containing a single SQL/XML value, you could use:

SELECT XMLELEMENT(NAME "user", MAX(user.Name), XMLAGG(XMLFOREST(phonenumber.phone_number)) FROM user LEFT OUTER JOIN phonenumber ON user.user_id = phonenumber.user_id GROUP BY user.user_id

XMLFOREST produces the phone_number elements (but is also suited for producing lots of concatinated elements), XMLAGG aggregates each of the phone_number elements under a root user element, which is returned for each grouping.

see also the Reference Guide, http://www.postgresql.org/docs/current/static/functions-xml.html, http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0022189.html, etc.  When possible we are following the SQL 2003 spec

[markaddleman]

Thanks, Steve.  As I was going through the Teiid reference guide, I was looking at the XML functions without (at first) realizing they were only the scalar functions.  It looks like XMLAGG was the piece I was missing.

[markaddleman]

Ok, so my real use case involves a n-level deep hierarchy involving n-tables.  When I try to nest XMLAGG functions, I get "Nested aggregate expressions are not allowed" error from Teiid.  I can't find reference to any limitations in the postgres docs or elsewhere (I don't have access to the official spec).  Is this a Teiid limitation? 

[rareddy]

Can you provide a sample query?

[shawkins]

Mark,

That's just a standard aggregate limitation, just as if you entered an expression like max(max(col)), a database like postgresql would also bark at you.  There are a couple of approaches you can take.  The most straightforward would be to use correlated subqueries:

SELECT XMLELEMENT(NAME "user", MAX(user.Name), XMLAGG(XMLELEMENT(NAME "phone_number", phonenumber.phone_number, (SELECT XMLELEMENT(... XMLAGG ... ))) FROM user LEFT OUTER JOIN phonenumber ON user.user_id = phonenumber.user_id GROUP BY user.user_id

The nested SELECT can inturn use an XMLAGG to produce a single scalar result, that is added as a child of the phone_number element. The original query could have been written using a correlated subquery as well, but it's generally prefurable to use a join when possible.  You can also look at using XML document models from Teiid Designer.

Steve

[markaddleman]

Cool.  Thanks for the pointers!

[markaddleman]

fwiw, I found a pretty decent site for SQL/XML that includes some good examples:  http://courses.ischool.berkeley.edu/i290a-2/s06/week2/homework-week2.htm