如何从Oracle中获取格式化的XML [英] How to get formatted XML out of Oracle

问题描述

我对Oracle缺乏经验，无法将数据导出为XML.我设法使该查询正常工作，但是XML的格式似乎是固定的，因此对我不起作用.这是查询:

I'm inexperience with Oracle and am having trouble exporting my data as XML. I've managed to get this query working, but the format of the XML seems fixed and won't work for me. Here's the query:

SELECT value(em).getClobVal() AS "output"
    FROM table(XMLSequence(Cursor
      (   
          SELECT * FROM UserMain
       )
)) em

我从中得到的是:

  <ROW><STATUS>Active</STATUS><NAME>Joe Smith<NAME><PHONE>234-2345</PHONE>...</ROW>
  <ROW><STATUS>Inactive</STATUS><NAME>Sally Smith<NAME><PHONE>234-4444</PHONE>...</ROW>
  etc.

但是我想要的是这样，在输出的外部带有XML标记"ROWS"，如下所示:

But what I want is this, with the XML tags "ROWS" around the outside of the output, as shown here:

  <ROWS>
      <RECORD><STATUS>Active</STATUS><NAME>Joe Smith<NAME><PHONE>234-2345</PHONE>...</RECORD>
      <RECORD><STATUS>Inactive</STATUS><NAME>Sally Smith<NAME><PHONE>234-4444</PHONE>...</RECORD>
      etc.
  </ROWS>

那我该如何处理查询以更改为并在输出周围放置外部标签?

So what do I need to do with my query to change to and put outer tags around the output?

推荐答案

几年前，我发现了一个窍门.如果使用XMLTYPE，并使用Oracle transform函数将某些XSL模板应用于此XMLTYPE，则会发生意外的行为:XML格式化.这是非常出乎意料的，并且从某些角度来看很有趣.

There is a trick I've found some years ago. If you use the XMLTYPE and apply some XSL template to this XMLTYPE using Oracle transform function, there is an unexpected behaviour: XML becomes formatted. It is very unexpected and from some point of view funny.

此procedure可以达到目的:

procedure beautify(xmlout in out nocopy clob)
  is
    xml    xmltype := new xmltype(xmlout);
    xsl    xmltype := new xmltype('<?xml version="1.0" encoding="iso-8859-1"?><DOCUMENT></DOCUMENT>');
    tmp    xmltype;
begin
  tmp := xml.transform(xsl,null);
  xmlout := xml.getclobval;

  if tmp is null then null; end if;
end;

当然，您应该将有效的XMLTYPE传递给函数，例如在您的情况下，应该是

Of course, you should pass to the function a valid XMLTYPE, e.g. in your case it should be kind of

<?xml version="1.0" encoding="iso-8859-1"?>
<DOCUMENT>
  <ROW><STATUS>Active</STATUS><NAME>Joe Smith<NAME><PHONE>234-2345</PHONE>...</ROW>
  <ROW><STATUS>Inactive</STATUS><NAME>Sally Smith<NAME><PHONE>234-4444</PHONE>...</ROW>
</DOCUMENT>

请注意，encoding="iso-8859-1"用于德语，您应将其更改为使用编码以达到目的.

Please, note that encoding="iso-8859-1" is used for German language, you should change it to use the encoding for your purposes.

如果要在查询中使用它，可以创建一个function而不是procedure.另外，您可以在格式化之前添加其他行，并在格式化之后将其删除.

If you want to use this in query you can create a function instead of procedure. Also you can add the additional rows before formatting and remove it after formatting.

这在Oracle 11.2中很好用

This works well in Oracle 11.2

这篇关于如何从Oracle中获取格式化的XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！