从SQLPLUS生成xml时如何插入新行 [英] How to insert new line when generating xml from SQLPLUS

问题描述

使用

XMLAgg(XMLElement('student', ...)...)

将所有内容都吐到一行上.鉴于我有一个非常大的表，它在假脱机时达到了行长度限制.

spits out everything onto one line. Given that I have a very large table, it reaches line length limit when spooling.

我希望将每个 ...</student> 节点放在单独的行上.此页面建议使用 XMLText(x'0A') 插入新行，但 SQLPlus 似乎无法识别它.

I'd like to have each <student>...</student> node on a separate line. This page suggests using XMLText(x'0A') to insert new lines, but SQLPlus doesn't seem to recognize it.

我已经试过了:

set long 2000000000
set linesize 32767
set wrap on
set trimspool on

推荐答案

10g 中通常的技巧是在您正在执行的外部 xml 操作上添加 .extract('/*') 例如

The usual trick in 10g was to add .extract('/*') on the outer xml operation you were doing eg

xmlagg(....).extract('/*')

但这在 11g 中不起作用.对于使用 xsl 转换的跨版本兼容版本，请参见 从oracle数据库表中生成带有自定义XML标签的XML文件.

but that doesn't work in 11g. for a cross version compatible one using an xsl transform see Generate XML file with Customized XML tags out of oracle database table.

10.2.0.4:

SQL> create table foo (id) as select rownum from dual connect by level <= 2;

Table created.

SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') a from foo;

A
--------------------------------------------------------------------------------
<id>
  <id2>1</id2>
</id>
<id>
  <id2>2</id2>
</id>

SQL> select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo;
select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo
                                                                                                *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).transform(xmltype('<xsl:stylesheet version="1.0"
  2   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  3   <xsl:output omit-xml-declaration="yes" indent="yes"/>
  4   <xsl:template match="node()|@*">
  5    <xsl:copy>
  6     <xsl:apply-templates select="node()|@*"/>
  7    </xsl:copy>
  8   </xsl:template>
  9  </xsl:stylesheet>')) a from foo;

A
--------------------------------------------------------------------------------
<id>
  <id2>1</id2>
</id>
<id>
  <id2>2</id2>
</id>

和 11.2.0.2/3:

and 11.2.0.2/3:

SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') a from foo;

A
--------------------------------------------------------------------------------
<id><id2>1</id2></id><id><id2>2</id2></id>

SQL> select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo;

A
--------------------------------------------------------------------------------
<id>
  <id2>1</id2>
</id>
<id>
  <id2>2</id2>
</id>

SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).transform(xmltype('<xsl:stylesheet version="1.0"
  2   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  3   <xsl:output omit-xml-declaration="yes" indent="yes"/>
  4   <xsl:template match="node()|@*">
  5    <xsl:copy>
  6     <xsl:apply-templates select="node()|@*"/>
  7    </xsl:copy>
  8   </xsl:template>
  9  </xsl:stylesheet>')) a from foo;

A
--------------------------------------------------------------------------------

<id>
 <id2>1</id2>
</id>
<id>
 <id2>2</id2>
</id>

简而言之，要执行此版本不可知论，您应该使用 XSL.如果你只是为了临时的东西而尝试这个，那么 extract 在 10g 上输入更短，而 xmlserialize 在 11g 上输入更短.

in short, to do this version agnositc, you should use a XSL. if you're only trying this for adhoc stuff, then extract is shorter to type on 10g and xmlserializeis shorter on 11g.

这篇关于从SQLPLUS生成xml时如何插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！