将数据从表传输到XML对象表

If I have a table:

如果我有一张桌子:

create table thisTable (
    column1 varchar(20),
    column2 varchar(20),
    column3 varchar(20)
);

and I want to transfer data to a 1 column XML table:

我想把数据传输到1列XML表:

create table XMLTable (
    data1 sys.xmltype
);

<column2>
    <column1>..</column1>
    <column2>..</column2>
    <column3>..</column3>
</column2>

How would I do that?

我该怎么做呢?

2 个解决方案

#1

INSERT INTO XMLTABLE
SELECT
    XMLELEMENT(
        "column2",
        XMLELEMENT("column1", COLUMN1), XMLELEMENT("column2", COLUMN2), XMLELEMENT("column3", COLUMN3)
    )
FROM
    thisTable;

#2

You can insert it using below procedure.

您可以使用下面的过程插入它。

  declare 
    sourceTable varchar2(80) := 'THISTABLE';
    destTable varchar2(80) := 'XMLTABLE';
    destColumn varchar2(80) := 'data1';
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    colu varchar2(2000);
    vsql varchar2(2000) := ' select ';
begin



      for r in (select column_name from user_tab_columns where table_name = sourceTable order by column_id)
      loop
        vsql := vsql ||  ''' <' || r.column_name || '>'' || ' || r.column_name || ' || ''</' || r.column_name || '> '' || ' ;       

      end loop;

      vsql := substr(vsql, 0 ,length(vsql)-4);
      vsql :=  vsql  ||' as x From ' || sourceTable;


     open c for vsql ;
      loop
       FETCH c INTo colu;
       EXIT WHEN c%NOTFOUND;
            dbms_output.put_line(colu);
          execute immediate '  insert into ' ||  destTable || ' values (xmltype(''<column2>' || colu || '</column2>'')) ';

      end loop;
      close c;

end;