title: XML import to Oracle with SQL Developer
body: <p>I would like to import an XML file to Oracle SQL with SQL developer. The XML has more than one node, my XML structure:</p>
  
  <pre><code>&lt;"&lt;"SPECTRAEXCHANGE&gt;"&gt;"
  &lt;"&lt;"APPLICATION&gt;"&gt;"
  &lt;"&lt;"SV_SV_ID&gt;"&gt;"kClong&lt;"&lt;"/SV_SV_ID/&gt;"&gt;"
  &lt;"&lt;"SS_SS_ID&gt;"&gt;"kClong&lt;"&lt;"/SS_SS_ID/&gt;"&gt;"
  &lt;"&lt;"AP_NAME&gt;"&gt;"kCstring (64)&lt;"&lt;"/AP_NAME/&gt;"&gt;"
  &lt;"&lt;"AP_PRJ_IDENT&gt;"&gt;"kCstring (32)&lt;"&lt;"/AP_PRJ_IDENT/&gt;"&gt;"
  &lt;"&lt;"STATION&gt;"&gt;"
  &lt;"&lt;"TCS_NAME&gt;"&gt;"kCstring (64)&lt;"&lt;"/TCS_NAME/&gt;"&gt;"
  &lt;"&lt;"TCS_CALL&gt;"&gt;"kCstring (256)&lt;"&lt;"/TCS_CALL/&gt;"&gt;"
  &lt;"&lt;"HORIZONTAL_ELEVATIONS&gt;"&gt;"
  &lt;"&lt;"HORIZONTAL_ELEVATION&gt;"&gt;"
  &lt;"&lt;"HE_AZIMUT&gt;"&gt;"kCdouble&lt;"&lt;"/HE_AZIMUT/&gt;"&gt;"
  &lt;"&lt;"HE_ELEVATION&gt;"&gt;"kCdouble&lt;"&lt;"/HE_ELEVATION/&gt;"&gt;"
  &lt;"&lt;"/HORIZONTAL_ELEVATION/&gt;"&gt;"
  &lt;"&lt;"/HORIZONTAL_ELEVATIONS/&gt;"&gt;"
  &lt;"&lt;"TRANSMITTER&gt;"&gt;"
  &lt;"&lt;"EQP_EQUIP_NAME&gt;"&gt;"kCstring (128)&lt;"&lt;"/EQP_EQUIP_NAME/&gt;"&gt;"
  &lt;"&lt;"EQP_EQUIP_TYPE&gt;"&gt;"kCstring (16)&lt;"&lt;"/EQP_EQUIP_TYPE/&gt;"&gt;"
  &lt;"&lt;"FREQUENCY&gt;"&gt;"
  &lt;"&lt;"EFL_FREQ&gt;"&gt;"kCdouble&lt;"&lt;"/EFL_FREQ/&gt;"&gt;"
  &lt;"&lt;"COORDINATED_FREQUENCY&gt;"&gt;"
  &lt;"&lt;"COF_DAT&gt;"&gt;"kWrDate&lt;"&lt;"/COF_DAT/&gt;"&gt;"
  &lt;"&lt;"/COORDINATED_FREQUENCY/&gt;"&gt;"
  &lt;"&lt;"/FREQUENCY/&gt;"&gt;"
  &lt;"&lt;"/TRANSMITTER/&gt;"&gt;"
  &lt;"&lt;"/STATION/&gt;"&gt;"
  &lt;"&lt;"/APPLICATION/&gt;"&gt;"
  &lt;"&lt;"/SPECTRAEXCHANGE/&gt;"&gt;" 
  </code></pre>
  
  <p>First I created the table SPECTRAEXCHANGE and than I want to use the following commands:</p>
  
  <pre><code>INSERT INTO TABLENAME(SV_SV_ID,
  SS_SS_ID,
  AP_NAME,
  AP_PRJ_IDENT )
  WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
  SELECT
  extractValue(value(x),'/APPLICATION/SV_SV_ID') SV_SV_ID /* value will be kClong */
  ,extractValue(value(x),'/APPLICATION/SS_SS_ID') SS_SS_ID /* value will be kClong*/
  ,extractValue(value(x),'APPLICATION/AP_NAME') AP_NAME /* value will be kCstring (64)*/
  ,extractValue(value(x),'APPLICATION/AP_PRJ_IDENT') AP_PRJ_IDENT
  FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION'))) x;
  
  
  UPDATE SPECTRAEXCHANGE SET TCS_NAME = extractValue(value(x),'/APPLICATION/STATION/TCS_NAME'),
  TCS_CALL = extractValue(value(x),'/APPLICATION/STATION/TCS_CALL')
  WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
  FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION/STATION'))) x;
  </code></pre>
  
  <p>etc.. </p>
  
  <p>And in UPDATE session I got error message:
  00933. 00000 - "SQL command not properly ended" </p>
  
  <p>And I tried this for UPDATE session:</p>
  
  <pre><code>UPDATE SPECTRAEXCHANGE SET
  TCS_NAME = x.TCS_NAME,
  TCS_CALL = x.TCS_CALL
  WITH t AS (SELECT xmltype(bfilename('TEST_DIR','yourXMLFileName.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
  SELECT
  extractValue(value(x),'APPLICATION/STATION/TCS_NAME') TCS_NAME
  ,extractValue(value(x),'APPLICATION/STATION/TCS_CALL') TCS_CALL
  FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/SPECTRAEXCHANGE/APPLICATION/STATION'))) x; 
  </code></pre>
  
  <p>but unfortunatly it doesn't work...
  so can you help me with update part? or give me other tutorial to import xml with more than one node.
  thanks.
  Milan</p>