将DB2 SQL XML查询输出到唯一的XML文件 [英] Output a DB2 SQL XML query to a unique XML file

问题描述

简化，我使用SQL查询生成一组XML元素，如：

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>
...

现在我需要将这个序列包装到一个主要的项目元素，并输出一个很好的缩进的XML文件。

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

我已经尝试过如下：

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

我得到的东西如下：

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

XMLAGG工作（将所有内容都包装到一个主要的项目元素中）。然而，有了10万个项目，db2抱怨SQL0433N Value...太久了。 SQLSTATE = 22001

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"

此外，output.xml不缩进（一切都在一行）。

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

实现这一点的正确方法是什么？

What is the correct way to achieve this?

Regards，
David

Regards, David

推荐答案

看起来你需要创建一个临时表，手动打开和关闭标签以及查询结果（一定要添加一些空格来缩小查询结果）如果您有一个ID列，并将表格中的所有内容都放在正确的顺序中，那么您只需要查询表（不带id，但按id排序），并将结果保存到文件中。

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.

这篇关于将DB2 SQL XML查询输出到唯一的XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！