Output a DB2 SQL XML query to a unique XML file

Question

Simplifying, I produce a set of XML elements with a SQL query such as:

SELECT XMLELEMENT(NAME "project", project) FROM project;
<project>project1</project>
<project>project2</project>
...

Now I need to wrap this sequence into a main "projects" element, and output this in a nice indented XML file.

I've tried as follows:

db2 -x "SELECT XMLSERIALIZE(CONTENT XMLELEMENT(NAME "projects", XMLAGG(XMLELEMENT(NAME "project", project))) AS CLOB INCLUDING XMLDECLARATION) FROM project" >output.xml

And I get something like:

<projects><project>project1</project><project>project2</project>...</projects>

The XMLAGG works (it has wrapped everything into a main projects element). However, with 100k thousand projects, db2 complains with "SQL0433N Value "... is too long. SQLSTATE=22001"

Also, output.xml is not indented (everything is in one line).

What is the correct way to achieve this?

Regards, David

Answer 1

Looks like you need to create a temporary table and put the opening and closing tag manually in there as well as the results from your query (be sure to add some spaces for the indention of your query results). If you have an ID column and put everything in the right order in the table, then you just need to query the table ( without id, but ordered by id) and save the results to a file.

Alternatively, you have a stored procedure go over the result of your query and output it in the formatted way. This might be slower than the first solution but needs probably less memory.

Last option you use your original query and post process your results, with an external tool.

Answer 2

You may not get the exact indentation you want, but you'll at least get one guaranteed line break per row if you don't XMLAGG the entire document into a single huge CLOB.

A common table expression is a convenient way of specifying a sorting column that does not have to appear in the final select statement.

WITH xmlheader( xmlrow, sort_seq ) AS ( VALUES ( 
' <?xml version="1.0" ?>'|| CHR( 10 ) || '<projects>', 1 ))
,
xmlbody( xmlrow, sort_seq ) AS ( VALUES( 
'<project>Replace this with your XMLSERIALIZE query, '
|| 'but only focus on generating XML for one project per row. '
|| 'This will enable you to specify a narrower data type, such AS VARCHAR(1000) '
|| 'for each row, and you will get each separate project '
|| 'followed by the normal newline at the end of each SQL row. '
|| 'No XMLAGG is necessary in this query.</project>', 2))
,
xmlfooter( xmlrow, sort_seq) AS ( VALUES( '</projects>', 3 ))

SELECT xmlrow FROM (
    SELECT xmlrow, sort_seq FROM xmlheader
    UNION ALL 
    SELECT xmlrow, sort_seq FROM xmlbody
    UNION ALL 
    SELECT xmlrow, sort_seq FROM xmlfooter
) AS fullxml 
ORDER BY sort_seq