[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Loading the data from XML file

I have received a XML file from one of my vendors.  I would like to load the data from this XML file to different database which has different table stucture.  Please let me know what steps should I take to proceed.  I have never worked with XML.
0
dnabatra
Asked:
dnabatra
  • 8
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
Depending on the specifics in might be as simple as using the insertXML call.  If things get to complicated you might have to use the DOM to loop through each node in the XML.

If you can post a sample XML template and sample table definition we might be able to come up with an exat example.

Here's a sample file that I had from a previous test.  It might get you started:
=================================================
--
--The following example adds a 'col2' node to the incoming XML and issues an XMLInsert with the new node
--
drop table tab1;
 
create table tab1(
      col1 number,
      col2 varchar2(100),
      col3 varchar2(100)
)
/

create or replace procedure xmlinsert( xmlDoc IN varchar2) is
      insCtx DBMS_XMLSave.ctxType;
      rows number;

      p xmlparser.parser;
      doc xmldom.DOMDocument;
      Elem xmldom.DOMElement;
      node xmldom.DOMNode;
      Text                        xmldom.DOMText;
      NodeList                  xmldom.DOMNodeList;
      result_doc varchar2(32000);
      row_counter      number;
      num_nodes      number;
begin
      p := xmlparser.newParser;
      xmlparser.parseBuffer(p,xmlDOC);
      doc := xmlparser.getDocument(p);
      xmldom.setVersion(doc, '1.0');
      NodeList := xmldom.getElementsByTagName(doc, 'tab1');
      num_nodes := xmldom.getLength(NodeList);

      for row_counter in 0..num_nodes-1 loop
            node := xmldom.item(NodeList, row_counter);
            doc := xmldom.makeDocument(node);

            Elem := xmldom.createElement( doc, 'col2' );
            node := xmldom.appendChild(node,xmldom.makeNode(Elem));
            Text := xmldom.createTextNode(doc,'qqq');
            node := xmldom.appendChild(node,xmldom.makeNode(Text));
      end loop;

      xmldom.writeToBuffer(doc, result_doc);
 
      insCtx := DBMS_XMLSave.newContext('tab1');
      DBMS_XMLSave.SetIgnoreCase(insCtx,1);
      rows := DBMS_XMLSave.insertXML(insCtx, result_doc);
      DBMS_XMLSave.closeContext(insCtx);

end;
/

show errors

select * from tab1;

call xmlinsert('
<row>
<tab1>
      <col1>123</col1>
      <col3>Some Text</col3>
</tab1>
</row>
');

select * from tab1;

0
 
dnabatraAuthor Commented:
Thanks...Would this work for Oracle 8i?
0
 
slightwv (䄆 Netminder) Commented:
It should.  I think I remember that there were some package name changes from 9i to 9i but I don't think this was one of them.  Check the docs (Or just try the posted sample).
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
slightwv (䄆 Netminder) Commented:
oops..... (It's Monday and I haven't had any alcohol yet...... at least that's my story)

change:
changes from 9i to 9i

to:
changes from 8i to 9i
0
 
schwertnerCommented:
this is an example how to load data in a table from XML doc:
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect xml/xml@test

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);

Table created.

SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
  2     insCtx DBMS_XMLSave.ctxType;
  3     rows number;
  4   begin
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  7      dbms_output.put_line(to_char(rows) || ' rows inserted');
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
  9  end;
 10  /

Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');

PL/SQL procedure successfully completed.

SQL> select * from empleados;

     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800

        20 John Calvach                   Principal Support Consultant
       900

        30 Louis Bald                     Technical Specialist
       400


     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500

        50 George Monk                    Support Consultant
       200


SQL>



SQL> select SYS_XMLGEN(empid) FROM empleados;

SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>

<?xml version="1.0"?>
<EMPID>20</EMPID>

<?xml version="1.0"?>
<EMPID>30</EMPID>

<?xml version="1.0"?>
<EMPID>40</EMPID>

SYS_XMLGEN(EMPID)

<?xml version="1.0"?>
<EMPID>50</EMPID>


SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM
0
 
dnabatraAuthor Commented:
If I have multiple tables in XML file with one to one or one to (zero or many) relationship, what should I do in this scenario?  Just loop through?
0
 
slightwv (䄆 Netminder) Commented:
Once you start getting into complex XML, its probably easier to use the DOM and loop through it.
0
 
dnabatraAuthor Commented:
I am still confused, I am going to read a book on XML.  Are there any function which can used to identify the starting of new entity or based on different elements/tags I can insert them into different tables?
0
 
slightwv (䄆 Netminder) Commented:
There's not a function per say.....  What you need to do is loop through the nodes.  There are many ways to go through the nodes of an XML doc.  Depending on the complexity of the XML it might be straight forward or quite complex.  Without the specifics of the XML and exactly how you need to parse it, a specific example is impossible.

Here's an example that loops through the nodes and based on the node name, puts the data into different tables (There may be easier ways.  This is what I came up with off the top of my head.):
--------------------------------------------------------
drop table tab1;
create table tab1(col1 varchar2(50));

drop table tab2;
create table tab2(col1 varchar2(50));


declare
      whichTable                        varchar2(100);
      myValue                              varchar2(100);
      p                                    xmlparser.parser;
      doc                                    dbms_xmldom.DOMDocument;

      node                              dbms_xmldom.DOMNode;
      NodeList                        dbms_xmldom.DOMNodeList;

      num_nodes                        number;
      row_counter                        number;

begin

            p := xmlparser.newParser;
            xmlparser.parseClob(p,'<MyXML><tab1data>Hello</tab1data><tab2data>world</tab2data></MyXML>');
            doc := xmlparser.getDocument(p);
            xmlparser.freeparser(p);


            Node := dbms_xmldom.makeNode(doc);
            Node := xslprocessor.selectSingleNode(Node,'//MyXML');
            NodeList := xslprocessor.selectNodes(Node,'*');
            num_nodes := dbms_xmldom.getLength(NodeList);


            for row_counter in 0..num_nodes-1 loop

                  node := dbms_xmldom.item(NodeList, row_counter);

                  dbms_output.put_line('Name: ' || dbms_xmldom.getnodename(Node) );

                  case dbms_xmldom.getnodename(Node)
                        when 'tab1data' then
                              whichTable := 'tab1';
                        when 'tab2data' then
                              whichTable := 'tab2';
                  end case;

                  node := xmldom.getFirstChild(node);

                  dbms_output.put_line('Value: ' || dbms_xmldom.getnodevalue(Node) );
                  myValue := dbms_xmldom.getnodevalue(Node);

                  case whichTable
                        when 'tab1' then
                              insert into tab1 values (myValue);
                        when 'tab2' then
                              insert into tab2 values (myValue);
                  end case;

            end loop;

            dbms_xmldom.freeDocument(doc);

end;
/

select * from tab1;
select * from tab2;

0
 
dnabatraAuthor Commented:
Thanks
I think I getting now...Is there any good book on this topic?  
Here is the structure of XML.
If you can little bit more elaborate this that would help me
<my_xml>
  <table_1_col_1 />
  <table_1_col_2>Some_value</table_1_col_2>
  <table_1_col_3>AAAA</table_1_col_3>
  <table_1_col_2>BBBB</table_1_col_2>
  <table_2_col_1>CCC</table_2_col_1>
  <table_2_col_2>DDD</table_2_col_2>
  <table_1_col_4 />
  <table_1_col_5 />
  <table_1_col_6 />
  <table_1_col_7 />
  <table_1_col_8 />
- <my_xml_1>
  <table_3_col_1>EEE</table_3_col_1>
  <table_3_col_1>FFF</table_3_col_1>
  <table_3_col_1 />
  <table_3_col_1>GGG</table_3_col_1>
  <table_4_col_1 />
  <table_5_col_1>HHHH</table_5_col_1>
  <table_5_col_1>IIII</table_5_col_1>
  <table_5_col_1>1</table_5_col_1>
  </my_xml_1>
- <my_xml_2>
  <table_6_col_1>EEE</table_6_col_1>
  <table_6_col_2>FFF</table_6_col_2>
  <table_6_col_3 />  
- <my_xml_3>
  <table_7_col_1>EEE</table_7_col_1>
  <table_7_col_2>FFF</table_7_col_2>
  <table_7_col_3 />
  </my_xml_3>
- <my_xml_3>
  <table_7_col_1>EEE</table_7_col_1>
  <table_7_col_2>FFF</table_7_col_2>
  <table_7_col_3 />
  </my_xml_3>
- <my_xml_4>
  <table_8_col_1>EEE</table_8_col_1>
  <table_8_col_2>FFF</table_8_col_2>
  <table_8_col_3 />
  - <my_xml_5>
    <table_9_col_1>EEE</table_9_col_1>
    <table_9_col_2>FFF</table_9_col_2>
    <table_9_col_3 />
    </my_xml_5>
  - <my_xml_6>
    <table_10_col_1>EEE</table_10_col_1>
    <table_10_col_2>FFF</table_10_col_2>
    <table_10_col_3 />
     </my_xml_6>
  </my_xml_4>
- <my_xml_4>
  <table_8_col_1>EEE</table_8_col_1>
  <table_8_col_2>FFF</table_8_col_2>
  <table_8_col_3 />
  - <my_xml_5>
      <table_9_col_1>EEE</table_9_col_1>
      <table_9_col_2>FFF</table_9_col_2>
      <table_9_col_3 />
      </my_xml_5>
    - <my_xml_6>
      <table_10_col_1>EEE</table_10_col_1>
      <table_10_col_2>FFF</table_10_col_2>
      <table_10_col_3 />
     </my_xml_6>
  </my_xml_4>
- <my_xml_7>
  <table_11_col_1>EEE</table_11_col_1>
  </my_xml_7>
  </my_xml_2>
  </my_xml>
+ <my_xml>
+ <my_xml>
+ <my_xml>
+ <my_xml>

0
 
slightwv (䄆 Netminder) Commented:
I know Oracle Press has a book on XML for Oracle.  Don't know if I can recommend it (I usually don't like Oracle Press books).  I find most of my information from the online docs and from, of all places, Microsoft.  The XML DOM is basically the same whereever you go.  This is why the Microsoft docs work (There are more DOM examples on the MSoft side).

I'll play with the provided XML and see what I can come up with.
0
 
slightwv (䄆 Netminder) Commented:
Question:

Is this a 1 time deal or is this going to be a recurring feed?

The reason I ask is that there is really no easy way to get there from here.  Since the data is scattered everywhere, I would probably recommend adding another level of complexity:  Performing a XML transformation (XSLT) to create a new XML doc that has the data in a more digestible manner.

i.e./
after the transformation you might end up with something like:
<my_new_xml>
  <all table 1 stuff>
       <col1>EEE</col1>
       <col2>EEE</col2>
...
   
0
 
dnabatraAuthor Commented:
Eventhough it is not one time deal, but the XML structure remains the same.  The difference would be some time the certain elements may not have the data.
0
 
slightwv (䄆 Netminder) Commented:
Empty/missing nodes aren't a big deal as long as when they are there, they are in the same place.

you will have problems if for example:

on day 1 you get:
<my_xml>
    <table1_col1>Hi</table1_col1>
<my_xml>

and on day 2 you get:
<my_xml>
   <someothernode>
      <table1_col1>Hi</table1_col1>
   </someothernode>
<my_xml>

Since you already stated that the layout will always remain the same. Not to worry.

I'm still thinking that the best appraoch would be to transform the incoming doc to something more useable to your relational structure and then using something like the stub I provided previously to perform the inserts.

If you end up with a doc like:
<my_xml>
    <table1_stuff>
       <tab1_col1>Hi</tab1_col1>
       <tab1_col4>Hi</tab1_col4>
    </table1_stuff>
    <table2_stuff>
       <tab2_col1>Hi</tab2_col1>
       <tab2_col8>Hi</tab2_col8>
    </table2_stuff>
</my_xml>

It will be a lot easier to loop through the secondary nodes and parse all the columns at 1 time.  Using the above template you would just loop through table1_stuff and table2_stuff and all columns are right there.



0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now