Transfer data from table into XML object table

Question

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:

create table XMLTable (
    data1 sys.xmltype
);

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

How would I do that?

Husqvik · Accepted Answer · 2015-11-18 03:49:11Z

3

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

answered Nov 18, 2015 at 3:49

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wolfgang Over a year ago

This is not working. I tried it and in xmltable only XMLTYPE is coming as output.

Husqvik Over a year ago

Because the result is XMLTYPE not VARCHAR2/CLOB; it depends on application how it's presented. SQL*Plus shows XML document. Try SELECT TO_CLOB(DATA1) FROM XMLTABLE

Wolfgang Over a year ago

Thank @Husqvik. It is working perfectly. +1 from my side

dcieslak · Accepted Answer · 2015-11-18 04:40:57Z

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;

Collectives™ on Stack Overflow

Transfer data from table into XML object table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related