-
1. Re: New to SQL/XML: Convert traditional result set into XML doc?
markaddleman Mar 7, 2012 10:13 PM (in response to 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?
-
2. Re: New to SQL/XML: Convert traditional result set into XML doc?
shawkins Mar 8, 2012 10:59 AM (in response to markaddleman)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
-
3. Re: New to SQL/XML: Convert traditional result set into XML doc?
markaddleman Mar 8, 2012 11:37 AM (in response to shawkins)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.
-
4. Re: New to SQL/XML: Convert traditional result set into XML doc?
markaddleman Mar 8, 2012 3:38 PM (in response to shawkins)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?
-
5. Re: New to SQL/XML: Convert traditional result set into XML doc?
rareddy Mar 9, 2012 9:03 AM (in response to markaddleman)Can you provide a sample query?
-
6. Re: New to SQL/XML: Convert traditional result set into XML doc?
shawkins Mar 9, 2012 9:47 AM (in response to rareddy)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
-
7. Re: New to SQL/XML: Convert traditional result set into XML doc?
markaddleman Mar 10, 2012 9:36 AM (in response to shawkins)Cool. Thanks for the pointers!
-
8. Re: New to SQL/XML: Convert traditional result set into XML doc?
markaddleman Mar 10, 2012 5:55 PM (in response to 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