PLSQL: insert xml values into table without specifing columns

Question

I generated an xml containing all the rows of my table using dbms_xmlgen.getxmltype:

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

So I have an xml like this:

    <ROWSET>
    <ROW>
     <ID>1</ID>
     <SURNAME>Smith</SURNAME>
      <NAME>John</NAME>
      <ADDRESS>XXX</ADDRESS>
    </ROW>
    <ROW>
     <ID>2</ID>
     <SURNAME>Allen</SURNAME>
     <NAME>Jim</NAME>
     <ADDRESS>YYY</ADDRESS>
     </ROW>
   </ROWSET>

Is it possible to re-insert the values from XML above into the same table dinamically without specifing any column name?

Rene · Accepted Answer · 2019-07-01 13:50:38Z

2

dbms_xmlsave can do that for you. In it's most basic form

declare
   myxml clob;
   rowsv number;
   ctx dbms_xmlsave.ctxType;
begin null;
   my_xml := --  XML data
   ctx := dbms_xmlsave.newcontext('myTable');
   rows := dbms_xmlsave.insertxml(ctx,myxml);
   dbms_xmlsave.closecontext(ctx);
end;
/

Oracle documentation

answered Jul 1, 2019 at 13:50

Rene

10.6k5 gold badges37 silver badges48 bronze badges

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

1 Comment

Ork Over a year ago

It mainly worls but with date columns I receive this error: ORA-29532 oracle.xml.sql.OracleXMLSQLException Unparseable date: "02-LUG-19"

XING · Accepted Answer · 2019-07-02 07:40:40Z

1

Is it possible to re-insert the values from XML above into the same table dinamically without specifing any column name?

You can do as shown in below demo:

--Table having xml
CREATE TABLE xml_tab (  xml_data  XMLTYPE);

--Inserting xml                       
INSERT INTO xml_tab VALUES ( '<ROWSET>
                            <ROW>
                             <ID>1</ID>
                             <SURNAME>Smith</SURNAME>
                              <NAME>John</NAME>
                              <ADDRESS>XXX</ADDRESS>
                            </ROW>
                            <ROW>
                             <ID>2</ID>
                             <SURNAME>Allen</SURNAME>
                             <NAME>Jim</NAME>
                             <ADDRESS>YYY</ADDRESS>
                             </ROW>
                           </ROWSET>'
);
--Selecting xml
SELECT * FROM xml_tab;                        

--Table to insert records
CREATE TABLE xmtab (
    id        NUMBER,
    surname   VARCHAR2(50),
    name      VARCHAR2(50),
    address   VARCHAR2(50)
);

--Inserting into XMTAB
INSERT INTO XMTAB
SELECT xt.*
FROM xml_tab x,
    XMLTABLE('/ROWSET/ROW'
     PASSING x.xml_data
     COLUMNS
      ID          NUMBER       PATH 'ID',
      SURNAME     VARCHAR2(50) PATH 'SURNAME',
      NAME        VARCHAR2(50) PATH 'NAME',
      ADDRESS     VARCHAR2(50) PATH 'ADDRESS'
          ) xt
;

answered Jul 2, 2019 at 7:40

XING

9,9164 gold badges25 silver badges40 bronze badges

4 Comments

Ork Over a year ago

it's not the scope of the Question. Insert into XMTAB must be done dinamically without specifing columns.

XING Over a year ago

@Ork To extract the values from the XML you need to mention the column names. It's NOT possible to do without that. You can still do it but the data will be saved under one columns not individual columns. Means if you remove

COLUMNS       ID          NUMBER       PATH 'ID',       SURNAME     VARCHAR2(50) PATH 'SURNAME',       NAME        VARCHAR2(50) PATH 'NAME',       ADDRESS     VARCHAR2(50) PATH 'ADDRESS'

you still get the rows of type xmltype, which you can insert into table. Btw why you don't want to explicitly mention the columns ?

Ork Over a year ago

As you can see above, user @Rene suggested the solution and it is possible. The only problem with his solution is data column format. I need to do it because I've got backup procedures on multiple tables and I don't want to create a single procedure for every table I need to backup.

XING Over a year ago

I need to do it because I've got backup procedures on multiple tables . The worst part playing with XML is its not very handy and its really difficult to manipulate. Although Rene solution seems working for you bt i can still see some error being faced by you. If that has been resolved you can close the question by accepting Rene's answer

Collectives™ on Stack Overflow

PLSQL: insert xml values into table without specifing columns

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related