将 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 值"......太长了.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?

问候，大卫

推荐答案

看起来你需要创建一个临时表并在其中手动放置开始和结束标记以及查询的结果(一定要添加一些用于缩进查询结果的空格).如果您有一个 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屋！