从oracle表生成XML [英] generate XML from oracle tables

问题描述

需要创建一个通用的存储过程，该存储过程将表名作为输入参数，并使用以下格式创建xml文件. Xml文件名应为表名.

Need to create one generic stored procedure which will take table name as input parameter and create xml file with below format. Xml file name should be table name.

    <XML>
    <TABLENAME></TABLENAME>
    <RECORDS>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    </RECORDS>
    </XML> 


<TABLENAME>  = actual table name(case should be same as in database)
<COLNAME> = actual column names from table(case should be same as in database)
Keep other tags as it is. Repeat <RECORD> tag for each row retrieved.

推荐答案

Oracle具有内置功能，可以将表的内容转换为XML:

Oracle has a built-in function to get the contents of a table as XML:

create table t42(id number, str varchar2(10));
insert into t42 values (1, 'AA');
insert into t42 values (2, 'BB');

select dbms_xmlgen.getxmltype('select * from t42')
from dual;

DBMS_XMLGEN.GETXMLTYPE('SELECT*FROMT42')
----------------------------------------
<ROWSET>
 <ROW>
  <ID>1</ID>
  <STR>AA</STR>
 </ROW>
 <ROW>
  <ID>2</ID>
  <STR>BB</STR>
 </ROW>
</ROWSET>

您可以在此周围添加自己的标签；可以作为查询来完成，但是由于您要使用存储过程:

You can add your own tags around that; could be done as a query but since you want a stored procedure:

create or replace function table_to_xml(table_name in varchar2) return xmltype as
  xml xmltype;
begin
  select xmlelement("XML",
      xmlelement(evalname(table_name),
        dbms_xmlgen.getxmltype('select * from "' || table_name || '"')))
  into xml
  from dual;

  return xml;
end table_to_xml;
/

select table_to_xml('T42') from dual;

TABLE_TO_XML('T42')
----------------------------------------
<XML><T42><ROWSET>
  <ROW>
    <ID>1</ID>
    <STR>AA</STR>
  </ROW>
  <ROW>
    <ID>2</ID>
    <STR>BB</STR>
  </ROW>
</ROWSET>
</T42></XML>

所以这具有您想要的结构(我想，但是请参阅下文)，但是具有ROWSET和ROW而不是RECORDS和RECORD. 可能无关紧要，这取决于您是否还在为该接口开发格式.如果没关系，则可以进一步执行重命名这些节点，或者-更有用的是-使用dbms_xmlgen过程setrowsettag和setrowtag，它们在您的过程中很简单(并在下面进行了演示).

So this has the structure you want (well, I think, but see below), but has ROWSET and ROW instead of RECORDS and RECORD. That might not matter, it depends whether you're developing the format for this interface still. If it does matter then you can apply a further step to rename those nodes, or - more helpfully - use the dbms_xmlgen procedures setrowsettag and setrowtag, which is simple in your procedure (and demonstrated below).

我假设您显示为<TABLENAME></TABLENAME>是一个错误，并且您想要该标记中的记录.如果不是，并且由于某些原因您确实希望这样做，请将函数中的查询更改为:

I'm assuming what you showed as <TABLENAME></TABLENAME> was a mistake, and you want the records within that tag. If not, and you really do want that for some reason, change the query in the function to:

  select xmlelement("XML",
      xmlconcat(xmlelement(evalname(table_name), null),
      dbms_xmlgen.getxmltype('select * from "' || table_name || '"')))
  into xml
  from dual;

然后您可以按照通常的方式将其写出到文件中；如果从SQL * Plus等进行调用，则可以选择并假脱机，或者如果根本不希望它返回，则可以添加UTL_FILE指令以从过程中写入文件，但这必须是到数据库服务器上的目录对象，这可能不方便.

You can then write that out to a file any way you normally would; if you're calling from SQL*Plus etc. you could select and spool, or if you don't want it returned at all you could add UTL_FILE directive to write the file from within the procedure, but that would have to be to a directory object on the DB server, which might not be convenient.

主要是出于我自己的利益，因为我对XML的使用不多:

Mostly for my own benefit as I don't do a lot with XML:

create or replace procedure table_to_xml_file(table_name in varchar2) as
  ctx dbms_xmlgen.ctxhandle;
  clb clob;
  file utl_file.file_type;
  buffer varchar2(32767);
  position pls_integer := 1;
  chars pls_integer := 32767;
begin
  ctx := dbms_xmlgen.newcontext('select * from "' || table_name || '"');
  dbms_xmlgen.setrowsettag(ctx, 'RECORDS');
  dbms_xmlgen.setrowtag(ctx, 'RECORD');

  select xmlserialize(document
        xmlelement("XML",
          xmlelement(evalname(table_name),
            dbms_xmlgen.getxmltype(ctx)))
      indent size = 2)
  into clb
  from dual;

  dbms_xmlgen.closecontext(ctx);

  file := utl_file.fopen('<directory>', table_name || '.xml', 'w', 32767);
  while position < dbms_lob.getlength(clb) loop
    dbms_lob.read(clb, chars, position, buffer);
    utl_file.put(file, buffer);
    utl_file.fflush(file);
    position := position + chars;
  end loop;
  utl_file.fclose(file);
end table_to_xml_file;
/

使用exec table_to_xml_file('T42')运行时，这将在<directory>目录对象指向的服务器目录中生成一个名为T42.xml的文件，该文件包含:

When run with exec table_to_xml_file('T42'), this produces a file called T42.xml in the server directory pointed to by the <directory> directory object, which contains:

<XML>
  <T42>
    <RECORDS>
      <RECORD>
        <ID>1</ID>
        <STR>AA</STR>
      </RECORD>
      <RECORD>
        <ID>2</ID>
        <STR>BB</STR>
      </RECORD>
    </RECORDS>
  </T42>
</XML>

顺便说一句，我在dbms_xmlgen.getxmltype调用中的select中在表名两边加上了双引号.这将满足表名的大小写应与数据库中的大小写相同"的要求；必须在正确的情况下将其传递给过程，否则它将出错.这比尝试以某种方式在过程中更正大小写要容易得多，这很尴尬，或者如果您有两个名称相同的表，那么这是不可能的.列名将始终使用正确的大小写.

Incidentally, I've put double-quotes around the table name in the select inside the dbms_xmlgen.getxmltype call. That's to meet the 'case should be same as in database' requirement for the table name; it has to be passed to the procedure in the correct case or it will error. That's simpler than trying to correct the case within the procedure somehow, which would be awkward, or impossible if you had two tables with the same name apart from the case. The columns names will be in the correct case anyway.

这篇关于从oracle表生成XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！