×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Import/load XML file (record) to a normal table using Oracle 10g

Import/load XML file (record) to a normal table using Oracle 10g

Import/load XML file (record) to a normal table using Oracle 10g

(OP)
Hi Guys,

I have this xml file (see attached). The idea is that using a normal oracle table (no xmltype datatype field), I want to load the record(s)
on the xml file to this table but ONLY records for <OppfLegemiddelMerkevare>, and ONLY extract 3 elements (or whatever is that called) from this namely:

example:

<Id>ID_C55B1496-8342-43DA-96B4-0503D56BE035</Id> as CODE
<Varenavn>Klorhexidin SA</Varenavn> as NAME
<NavnFormStyrke>Klorhexidin SA Øyedr 0,2 mg/ml</NavnFormStyrke> as DESCRIPTION

the rest are irrelevant.

Structure of my oracle table is:

MY_ORACLE_TBL
--------------------
CODE VARCHAR2(50)
NAME VARCHAR2(100)
DESCRIPTION VARCHAR2(2000)


What I have so far is this now based on my reseach (google):

insert into MY_ORACLE_TBL (CODE, NAME, DESCRIPTION)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'mymxl.xml'), nls_charset_id('CHAR_CS'))
columns CODE varchar2(50) path 'Id',
NAME varchar2(100) path 'Varenavn',
DESCRIPTION varchar2(2000) path 'NavnFormStyrke');

but this always gives me an error:

SQL Error: ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 1
00942. 00000 - "table or view does not exist"

100% sure XML_DIR is created on the server running oracle. The xml file as well is in there.

Please help.

TIA,
Yorge

RE: Import/load XML file (record) to a normal table using Oracle 10g

This looks like a permission problem. Check which Oracle user created the directory and ensure they have granted permissions to read this directory to other users as required.

In order to understand recursion, you must first understand recursion.

RE: Import/load XML file (record) to a normal table using Oracle 10g

(OP)
hello taupirho, tnx for the reply...grant fix it...the error is gone now but after execution, there are 0 rows inserted sad
can you please take a look at the code I posted and see what's wrong with it?

RE: Import/load XML file (record) to a normal table using Oracle 10g

(OP)
it should be from here: /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare

but im getting 0 records

RE: Import/load XML file (record) to a normal table using Oracle 10g

You have two ID nodes, one hanging off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare node and one off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare node. Which one are you trying to get ? Also both Varenavn and NavnFormStyrke are off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare nodes.

So I would say you need to amend your select statement to ensure that you get the correct pathnames of each of these nodes. Something like:-

nsert into MY_ORACLE_TBL (CODE, NAME, DESCRIPTION)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'mymxl.xml'), nls_charset_id('CHAR_CS'))
columns CODE varchar2(50) path 'Id', ***** (or maybe 'LegemiddelMerkevare/Id' ********
NAME varchar2(100) path 'LegemiddelMerkevare/Varenavn',
DESCRIPTION varchar2(2000) path 'LegemiddelMerkevare/NavnFormStyrke');

In order to understand recursion, you must first understand recursion.

RE: Import/load XML file (record) to a normal table using Oracle 10g

(OP)
hi taupirho, tried both pathnames but still zero rows inserted.
I tested for the ID only with this code:

insert into MY_ORACLE_TBL (CODE)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'myxml.xml'), nls_charset_id('CHAR_CS'))
columns Id varchar2(50) path 'Id'
);

0 rows inserted.

Is it possible for you to try this from your end using the xml file I attached?

TIA,
Yorge

RE: Import/load XML file (record) to a normal table using Oracle 10g

I think the issue is with the format of your XML. Consider this.

$ type myxml.xml
<KatLegemiddelMerkevare>
<OppfLegemiddelMerkevare>
<Id>ID_DF169A83-8C7D-4349-BD54-32DB56330CE0</Id>
<Tidspunkt>2013-08-29T12:47:06</Tidspunkt>
<Status V="A" DN="Aktiv oppføring" />
</OppfLegemiddelMerkevare>
</KatLegemiddelMerkevare>


SQL> l
1 SELECT
2 a.id
3 FROM
4 xmltable
5 (
6 'KatLegemiddelMerkevare'
7 PASSING
8 SYS.XMLType
9 (
10 bfilename('ISDADIR', 'myXML.xml'),
11 nls_charset_id('CHAR_CS')
12 )
13 COLUMNS id varchar2(50) PATH 'OppfLegemiddelMerkevare/Id'
14* ) a
SQL> /

ID
--------------------------------------------------
ID_DF169A83-8C7D-4349-BD54-32DB56330CE0


In order to understand recursion, you must first understand recursion.

RE: Import/load XML file (record) to a normal table using Oracle 10g

(OP)
hmmmmm, the file I attached is a copy from a client. Is it safe to say that the xml file is erroneous or something?

RE: Import/load XML file (record) to a normal table using Oracle 10g

Perhaps, its not well formed or something. I don't know. If I were you I would take my little sample XML file and add to it gradually until it becomes what you originally had and see how far you get before it breaks. I assume you managed to get the little example snippet I gave you to work ?

In order to understand recursion, you must first understand recursion.

RE: Import/load XML file (record) to a normal table using Oracle 10g

(OP)
yup, I did...I got it to work using your sample...tnx

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close