Home » SQL & PL/SQL » SQL & PL/SQL » Extracting data from XML file problem (11.2.0.2.0 )
Extracting data from XML file problem [message #567685] Thu, 04 October 2012 06:30 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi All,

I need to extract the values from XML file.
I tried with method described in below links -

http://www.orafaq.com/forum/t/139289/102589/
http://www.orafaq.com/forum/m/380259/102589/#msg_380259

My XML file is -
<?xml version="1.0" ?>
<!DOCTYPE main [
  <!ELEMENT main (DATA_RECORD*)>
  <!ELEMENT DATA_RECORD (STATE_CODE?,JOB_NAME?,WC_CODE?,JOB_ID?,STATUS?,LOG_MESSAGE?)+>
  <!ELEMENT STATE_CODE (#PCDATA)>
  <!ELEMENT JOB_NAME (#PCDATA)>
  <!ELEMENT WC_CODE (#PCDATA)>
  <!ELEMENT JOB_ID (#PCDATA)>
  <!ELEMENT STATUS (#PCDATA)>
  <!ELEMENT LOG_MESSAGE (#PCDATA)>
]>
<main>
  <DATA_RECORD>
    <STATE_CODE>NC</STATE_CODE>
    <JOB_NAME>6125.Tax Manager V</JOB_NAME>
    <WC_CODE>8810</WC_CODE>
    <JOB_ID>170605</JOB_ID>
    <STATUS>SUCCESS</STATUS>
    <LOG_MESSAGE>The Current and New WC code for State - NC and JOB - 6125.Tax Manager V is same.No Update required</LOG_MESSAGE>
  </DATA_RECORD>
  <DATA_RECORD>
    <STATE_CODE>NC</STATE_CODE>
    <JOB_NAME>6144.Cost Accountant IV</JOB_NAME>
    <WC_CODE>8810</WC_CODE>
    <JOB_ID>2199</JOB_ID>
    <STATUS>SUCCESS</STATUS>
    <LOG_MESSAGE>The Current and New WC code for State - NC and JOB - 6144.Cost Accountant IV is same.No Update required</LOG_MESSAGE>
  </DATA_RECORD>
  <DATA_RECORD>
    <STATE_CODE>NC</STATE_CODE>
    <JOB_NAME>6152.Credit/Collection Analyst II</JOB_NAME>
    <WC_CODE>8810</WC_CODE>
    <JOB_ID>1419</JOB_ID>
    <STATUS>SUCCESS</STATUS>
    <LOG_MESSAGE>The Current and New WC code for State - NC and JOB - 6152.Credit/Collection Analyst II is same.No Update required</LOG_MESSAGE>
  </DATA_RECORD>
  <DATA_RECORD>
    <STATE_CODE>NC</STATE_CODE>
    <JOB_NAME>6235.Sr Compensation & Benefits Specialist</JOB_NAME>
    <WC_CODE>8810</WC_CODE>
    <JOB_ID>283623</JOB_ID>
    <STATUS>SUCCESS</STATUS>
    <LOG_MESSAGE>Successfully update </LOG_MESSAGE>
  </DATA_RECORD>
  <DATA_RECORD>
    <STATE_CODE>NC</STATE_CODE>
    <JOB_NAME>6253.Benefits Analyst III</JOB_NAME>
    <WC_CODE>8810</WC_CODE>
    <JOB_ID>114</JOB_ID>
    <STATUS>SUCCESS</STATUS>
    <LOG_MESSAGE>The Current and New WC code for State - NC and JOB - 6253.Benefits Analyst III is same.No Update required</LOG_MESSAGE>
  </DATA_RECORD>
</main>



I tried like following -
SQL> SELECT *
  FROM v$version;  2

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> SELECT *
  FROM dba_directories
 WHERE directory_name = 'ADP_ORACLE';  2    3

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            ADP_ORACLE
/t11/prod/apps/apps_st/appl/cree/12.0.0/dataxfer/ADP_Oracle

SQL> SELECT EXTRACTVALUE (COLUMN_VALUE, '/DATA_RECORD/@STATE_CODE') AS state_code
  2       , EXTRACTVALUE (COLUMN_VALUE, '/DATA_RECORD/@JOB_NAME') AS job_name
  3       , EXTRACTVALUE (COLUMN_VALUE, '/DATA_RECORD/@WC_CODE') AS wc_code
  4       , EXTRACTVALUE (COLUMN_VALUE, '/DATA_RECORD/@JOB_ID') AS job_id
  5       , EXTRACTVALUE (COLUMN_VALUE, '/DATA_RECORD/@STATUS') AS status
  6       , EXTRACTVALUE (COLUMN_VALUE
  7                     , '/DATA_RECORD/@LOG_MESSAGE') AS log_message
  8    FROM TABLE
          (XMLSEQUENCE
  9   10                       (EXTRACT (XMLTYPE (BFILENAME ('ADP_ORACLE'
 11                                                   , 'CREE_HR_WC_CODE_UPD.xml'
 12                                                    )
 13                                        , NLS_CHARSET_ID ('WE8ISO8859P1')
 14                                         )
 15                               , '/main/DATA_RECORD'
 16                                )
 17                       )
 18            );

STA JOB_NAME                       WC_COD JOB_ID   STATUS   LOG_MESSAGE
--- ------------------------------ ------ -------- -------- --------------------------------------------------







The query is not returning any rows.
Thank you in advance.

Regards,
Ram




Re: Extracting data from XML file problem [message #567686 is a reply to message #567685] Thu, 04 October 2012 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove "@"

Regards
Michel
Re: Extracting data from XML file problem [message #567689 is a reply to message #567686] Thu, 04 October 2012 06:42 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi Michel,

Thank You very much for such quick response.
It working now.

Regards,
Ram
Re: Extracting data from XML file problem [message #567698 is a reply to message #567689] Thu, 04 October 2012 06:56 Go to previous message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"@" sign means you want the value inside the tags and not between the tags.
It would work if you had something like:
<DATA_RECORD STATE_CODE="NC" WC_CODE="8810" JOB_ID="1419" STATUS="SUCCESS"></DATA_RECORD>

Regards
Michel
Previous Topic: get the MAX length of the column value along with the value
Next Topic: Help with regexp_substr
Goto Forum:
  


Current Time: Thu Apr 17 07:30:29 CDT 2014

Total time taken to generate the page: 0.24213 seconds