oracle plsql how to parse XML and insert into table

Question

How to load a nested xml file into database table     lt  xml version  1 0    gt    lt person gt      lt row gt          lt name gt Tom lt  name gt          lt Address gt              lt State gt California lt  State gt              lt City gt Los angeles lt  City gt          lt  Address gt      lt  row gt      lt row gt          lt name gt Jim lt  name gt          lt Address gt              lt State gt California lt  State gt              lt City gt Los angeles lt  City gt          lt  Address gt      lt  row gt   lt  person gt           In this xml  person is the table name   name is the filed name  Tom is its filed value   Address is a subtable and state and city is two column inside Address  I want to insert the person row into person table  if it failed   do not insert into address table  This xml could be very big  What s the best solution to do this

User · Answer

CREATE OR REPLACE PROCEDURE ADDEMP      xml IN CLOB  AS BEGIN     INSERT INTO EMPLOYEE  EMPID EMPNAME EMPDETAIL CREATEDBY CREATED      SELECT          ExtractValue column value   ROOT EMPID   AS EMPID         ExtractValue column value   ROOT EMPNAME   AS EMPNAME         ExtractValue column value   ROOT EMPDETAIL   AS EMPDETAIL         ExtractValue column value   ROOT CREATEDBY   AS CREATEDBY         ExtractValue column value   ROOT CREATEDDATE   AS CREATEDDATE     FROM   TABLE XMLSequence  XMLType xml    XMLDUMMAY       COMMIT  END

User · Answer

You can load an XML document into an XMLType  then query it  e g    DECLARE   x XMLType    XMLType        lt  xml version  1 0    gt    lt person gt      lt row gt          lt name gt Tom lt  name gt          lt Address gt              lt State gt California lt  State gt              lt City gt Los angeles lt  City gt          lt  Address gt      lt  row gt      lt row gt          lt name gt Jim lt  name gt          lt Address gt              lt State gt California lt  State gt              lt City gt Los angeles lt  City gt          lt  Address gt      lt  row gt   lt  person gt     BEGIN   FOR r IN       SELECT ExtractValue Value p    row name text     as name            ExtractValue Value p    row Address State text     as state            ExtractValue Value p    row Address City text     as city     FROM   TABLE XMLSequence Extract x   person row     p       LOOP     -- do whatever you want with r name  r state  r city   END LOOP  END

User · Answer

select     FROM XMLTABLE   person row             PASSING                xmltype                    lt person gt                      lt row gt                          lt name gt Tom lt  name gt                          lt Address gt                              lt State gt California lt  State gt                              lt City gt Los angeles lt  City gt                          lt  Address gt                      lt  row gt                      lt row gt                          lt name gt Jim lt  name gt                          lt Address gt                              lt State gt California lt  State gt                              lt City gt Los angeles lt  City gt                          lt  Address gt                      lt  row gt                   lt  person gt                          COLUMNS               --describe columns and path to them                name  varchar2 20     PATH    name                 state varchar2 20     PATH    Address State                 city  varchar2 20     PATH    Address City         xmlt

[xml] oracle plsql: how to parse XML and insert into table

Examples related to xml

Examples related to oracle

Examples related to plsql

Examples related to insert

Examples related to xml-parsing