Home » Developer & Programmer » JDeveloper, Java & XML » XML to SDO_GEOMETRY object migration (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0, OS: Windows XP Professional)
XML to SDO_GEOMETRY object migration [message #413695] Fri, 17 July 2009 02:18 Go to next message
MSAM123
Messages: 29
Registered: June 2007
Junior Member
Hi All,
I have a requirement to migrate the data from XML to Oracle database. My database table has a SDO_GEOMETRY column.
Below are the table structure, XML File and the PL/SQL script.

Table Structure (TAB_FEAT):
UNIQUE_ID      VARCHAR2(50)
ORIENTATION    NUMBER(7,2)
SHAPE          SDO_GEOMETRY


XML File:
<?xml version="1.0" ?>
<FEATURE>
<UNIQUE_ID>L1_876_B2</UNIQUE_ID>
<GEOM>
<POINT>
<COORD>1234.123,4571.56</COORD>
<ORIENTATION>2.987</ORIENTATION>
</POINT>
</GEOM>
</FEATURE>


PL/SQL Script:
CREATE OR REPLACE PROCEDURE MIG_FEAT AS 
--DECLARE
  v_bfile   BFILE;
  v_clob    CLOB;
  v_parser  dbms_xmlparser.Parser;
  v_doc     dbms_xmldom.DOMDocument;
  v_nl      dbms_xmldom.DOMNodeList;
  v_n       dbms_xmldom.DOMNode;
  v_error   VARCHAR2(300);

  TYPE tab_type IS TABLE OF tab_feat%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
  v_bfile := BFileName('XML_DIR', 'FEAT.XML');
  dbms_lob.createtemporary(v_clob, cache=>FALSE);
  dbms_lob.open(v_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => v_clob,
                        src_lob  => v_bfile,
                        amount   => dbms_lob.getLength(v_bfile));
  dbms_lob.close(v_bfile);

  v_parser := dbms_xmlparser.newParser;

  dbms_xmlparser.parseClob(v_parser, v_clob);
  v_doc := dbms_xmlparser.getDocument(v_parser);

  dbms_lob.freetemporary(v_clob);
  dbms_xmlparser.freeParser(v_parser);

  v_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(v_doc),'/FEATURE');

  FOR cur_feat IN 0 .. dbms_xmldom.getLength(v_nl) - 1 LOOP
    v_n := dbms_xmldom.item(v_nl, cur_feat);
    t_tab.extend;

    dbms_xslprocessor.valueOf(v_n,'UNIQUE_ID/text()',t_tab(t_tab.last).unique_id);
    /*
    How to extract the ORIENTATION, X and Y Co-Ordinate Values
    */
  END LOOP;

  FOR cur_feat IN t_tab.first .. t_tab.last LOOP
    INSERT INTO tab_feat
    (unique_id,
     orientation,
     shape
     )
    VALUES
    (t_tab(cur_feat).unique_id,
    /* ORIENTATION and SHAPE columns to be populated*/
    );
  END LOOP;
  COMMIT; 

  dbms_xmldom.freeDocument(v_doc);

EXCEPTION
  WHEN OTHERS THEN
    v_error  := SQLCODE|| ' - '|| SQLERRM;
    DBMS_OUTPUT.PUT_LINE('Error:'||v_error);
    dbms_lob.freetemporary(v_clob);
    dbms_xmlparser.freeParser(v_parser);
    dbms_xmldom.freeDocument(v_doc);
END MIG_FEAT;


I'm able to migrate the attribute data from XML to database, but the problem is with the SDO_GEOMETRY column.

How can I read the ORIENTATION, X & Y Coordinate values from the XML file and insert in the database table.


Thanks,
Msam
Re: XML to SDO_GEOMETRY object migration [message #413699 is a reply to message #413695] Fri, 17 July 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to extract the ORIENTATION, X and Y Co-Ordinate Values

SQL> with
  2    data as (
  3      select xmltype('<?xml version="1.0" ?>
  4  <FEATURE>
  5  <UNIQUE_ID>L1_876_B2</UNIQUE_ID>
  6  <GEOM>
  7  <POINT>
  8  <COORD>1234.123,4571.56</COORD>
  9  <ORIENTATION>2.987</ORIENTATION>
 10  </POINT>
 11  </GEOM>
 12  </FEATURE>') val
 13      from dual
 14    )
 15  select extractvalue(value(t),'/POINT/ORIENTATION') orientation,
 16         extractvalue(value(t),'/POINT/COORD') coordinates
 17  from data,
 18       table(xmlsequence(extract(data.val, '/FEATURE/GEOM/POINT'))) t
 19  /
ORIENTATION COORDINATES
----------- --------------------
2.987       1234.123,4571.56

1 row selected.


WHEN OTHERS clause MUST end with RAISE.

Regards
Michel
Re: XML to SDO_GEOMETRY object migration [message #413973 is a reply to message #413699] Sun, 19 July 2009 23:39 Go to previous message
MSAM123
Messages: 29
Registered: June 2007
Junior Member
Michel,

Thank you for providing the solution.

Regards,
Msam
Previous Topic: extract from XML
Next Topic: Produce XML schema
Goto Forum:
  


Current Time: Thu Mar 28 20:16:51 CDT 2024