Hi all,
I'm having a crack at writing some user defined functions to output XML
directly from a mysql query. The best way I've come up with is using
subqueries with GROUP BY's. The functions will use libxml's xmlwriter to
generate an xml string. I know this method should work, because it can
also be done using CONCAT and GROUP_CONCAT functions. (Please have a
look at the code below)
I've looked on the internet, but the best thing I found is MyXML, which
is simply too limited (and looks not supported). Is anyone currently
already working on this or is MySQL AB itself working on XML output
functions.
I've also got a direct question. What is exactly the function of
group_concat_max_len? I got the impression that memory the size of
group_concat_max_len is allocated for each group_concat call.
If so, I hope to prevent doing the same using xmlwriter. I'm not sure,
but at the first glance it looks like xmlwriter allocates memory
dynamically.
I'm not sure about performance; using lots of group_concat's can really
slow a query down. But we'll just have to see after I've written some
code.
If anyone has any tips, remarks, etc, please let me know.
------
Proposed syntax:
SELECT xmlelement("root", xmlgroup(trunk.xmlnode)) FROM
(SELECT xmlelement("trunk", xmlgroup(leaf.xmlnode),
xmlattributes("id", trunk.id, "description", trunk.description)) AS
xmlnode FROM tree_trunk LEFT JOIN
(SELECT tree_trunk_id, xmlelement("leaf", NULL, xmlattributes("id",
tree_leaf.id, "description", tree_leaf.description)) AS xmlnode FROM
tree_leaf) AS leaf ON tree_trunk.id = leaf.trunk_id
GROUP BY tree_trunk.id) AS trunk
------
Simulation:
SELECT CONCAT_WS('', "<root>", GROUP_CONCAT(trunk.xmlnode SEPARATOR ''),
"</root>") FROM
(SELECT CONCAT_WS('', "<trunk id=", QUOTE(tree_trunk.id), "
description=", QUOTE(tree_trunk.description), ">",
GROUP_CONCAT(leaf.xmlnode SEPARATOR ''), "</trunk>") AS xmlnode FROM
tree_trunk LEFT JOIN
(SELECT tree_leaf.trunk_id, CONCAT_WS('', "<leaf id=",
QUOTE(tree_leaf.id), " description=", QUOTE(tree_leaf.description), "
/>") AS xmlnode FROM tree_leaf) AS leaf ON tree_trunk.id = leaf.trunk_id
GROUP BY tree_trunk.id) AS trunk
Output (query does not format xml):
<root>
<trunk id='1' description='ab'>
<leaf id='1' description='ab_aa' />
</trunk>
<trunk id='2' description='bc'>
<leaf id='2' description='bc_aa' />
<leaf id='3' description='bc_ab' />
</trunk>
<trunk id='3' description='cd'></trunk>
<trunk id='4' description='de'></trunk>
<trunk id='5' description='ef'>
<leaf id='4' description='ef_aa' />
</trunk>
<trunk id='6' description='fg'></trunk>
<trunk id='7' description='gh'>
<leaf id='5' description='gh_aa' />
</trunk>
<trunk id='8' description='hi'>
<leaf id='6' description='hi_aa' />
</trunk>
</root>
------
Met vriendelijke groet,
Arnold Daniels
Bean IT
