1 Reply Latest reply: Jun 18, 2013 10:58 AM by Jason_(A_Non) RSS

    Loading XML by using XMLSEQUENCE and EXTRACT

    Mark1970

      I've discovered that on Oracle 10gR2 Express Edition I can't use XMLTABLE but I can/must use XMLSEQUENCE and EXTRACT/EXTRACTVALUE for loading an XML file.

      Since it's my first time, I can't understand how to do.

      I report a simple example.

      I have this XML called myxml.xml

       

      <?xml version="1.0"?>

      <ACCOUNT_HEADER_ACK>

      <HEADER>

      <STATUS_CODE>100</STATUS_CODE>

      <STATUS_REMARKS>check</STATUS_REMARKS>

      </HEADER>

      <DETAILS>

      <DETAIL>

      <SEGMENT_NUMBER>2</SEGMENT_NUMBER>

      <REMARKS>rp polytechnic</REMARKS>

      </DETAIL>

      </DETAILS>

      </ACCOUNT_HEADER_ACK>

       

      I'd like an example of how using XMLSEQUENCE and EXTRACT/EXTRACTVALUE for loading a node of myxml.xml file stored in /mydirectory/.

      I believe it might be something like this:

       

      select extract('/mydirectory/myxml.xml','DETAIL')

      from dual;

       

      but I don't know.

       

      My target is how to load an external xml file in a xmltype column of a table and then scanning the column for extract the value to be inserted in the right tables.

       

      Thanks!