Home » Developer & Programmer » JDeveloper, Java & XML » Newbie! Please Help - XML to Relational Table (I have searched) (9.2.0.7)
icon5.gif  Newbie! Please Help - XML to Relational Table (I have searched) [message #420999] Thu, 03 September 2009 11:03 Go to next message
p-illist
Messages: 3
Registered: September 2009
Junior Member
Hello All,

I'll get right to the point...I have a PL/SQL procedure that I believe should work to get an xml file into a relational table, but it keeps giving me compile errors. When I fix them, other compile errors arise. Can somebody look at my procedure and tell me if it is something that should be able to work.

I ran this first
******************************
CREATE TABLE XML_IMPORT_STORE (import_date date, file_data XMLTYPE);
CREATE DIRECTORY XML_FILE_DIR AS '/pkg/misc/samaplx';

BEGIN
XMLLOAD (ach_dir => 'XML_FILE_DIR',
ach_file_name => 'ach_test.xml',
ach_table_name => 'XML_IMPORT_STORE');
END;
******************************************

This is the procedure:

CREATE OR REPLACE PROCEDURE XMLLOAD (ach_dir IN VARCHAR2,
ach_file_name IN VARCHAR2,
ach_table_name IN VARCHAR2) AS
l_bfile BFILE := BFILENAME(ach_dir,ach_file_name);
l_clob CLOB;
BEGIN
XMLLOAD (ach_dir => 'XML_FILE_DIR',
ach_file_name => 'ach_test.xml',
ach_table_name => 'XML_IMPORT_STORE');
DBMS_LOB.createtemporary (l_clob, TRUE);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);

EXECUTE IMMEDIATE 'INSERT INTO ' || ach_table_name || ' VALUES (XMLTYPE.createXML(:l_clob))' USING l_clob;
DBMS_LOB.freetemporary (l_clob);
COMMIT;

FOR i in ( select EXTRACTVALUE(VALUE(t), '/VENDOR/ID') vendor_id,
EXTRACTVALUE(VALUE(t), '/VENDOR/ACCOUNT') vendor_account,
EXTRACTVALUE(VALUE(t), '/VENDOR/DELIVERY') vendor_delivery
FROM TABLE(xmlSEQUENCE(EXTRACT(SELECT file_data FROM XML_IMPORT_STORE, '/CUSTOMERS/VENDOR'))) t )

LOOP

UPDATE VENDOR_ACH SET vendor_ach_account = i.vendor_account
AND vendor_delivery_account = i.vendor_delivery
WHERE vendor_ach_id = i.vendor_id;
COMMIT;
IF SQL%NOTFOUND THEN
INSERT INTO VENDOR_ACH VALUES (i.vendor_id,i.vendor_account,i.vendor_delivery);
END IF;

END LOOP;

END;
Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421002 is a reply to message #420999] Thu, 03 September 2009 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can somebody look at my procedure and tell me if it is something that should be able to work

If compile errors exist, then procedure won't work.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421006 is a reply to message #421002] Thu, 03 September 2009 11:23 Go to previous messageGo to next message
p-illist
Messages: 3
Registered: September 2009
Junior Member
Ok, I understand if it doesn't compile then it won't work (i'm not that dense lol) but I just wanted to know if the procedure I have setup is something that could work with some tweaks.

Oracle RDBMS:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421009 is a reply to message #420999] Thu, 03 September 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=ce651f30046e6aac5ae7da16d736fa08&t=search&srch="load+xml"&btn_submit=Search&field=subject&forum_limiter=&search_logic=AND&sort_order=DESC&author=
Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421010 is a reply to message #420999] Thu, 03 September 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many examples in "JDeveloper, Java & XML".

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and example of input file along with the result you want with these data.


Regards
Michel
Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421017 is a reply to message #420999] Thu, 03 September 2009 12:20 Go to previous message
p-illist
Messages: 3
Registered: September 2009
Junior Member
Nvmd guys...wayyyyy too many rules and unanswered questions out there on the board already. Thanx
Previous Topic: is embedded sql faster than refcursor?
Next Topic: retriving XML tags from response xml using pl/sql
Goto Forum:
  


Current Time: Fri Mar 29 01:03:50 CDT 2024