Home » SQL & PL/SQL » SQL & PL/SQL » generate xml from flat file
generate xml from flat file [message #258750] Mon, 13 August 2007 09:14 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a requirement that i have no idea what to do

i have a csv file, with data somewhat like this


"1","1","47784","mycol","mycol","user112",,,, --for company data

"2","1","mpay","John","smith",....-- user data



and tables for company and user
a user belongs to company

CREATE TABLE companyDATA ( 
  ORG_ID   NUMBER (16)   NOT NULL, 
  INTERFACE_ID  NUMBER (2)    NOT NULL, 
  ORG_DATA_XML  CLOB, 
  STATUS        NUMBER (2))

CREATE TABLE userDATA ( 
  USER_ID   NUMBER (16)   NOT NULL, 
  INTERFACE_ID   NUMBER (2)    NOT NULL, 
  USER_DATA_XML  CLOB          DEFAULT EMPTY_CLOB(), 
  STATUS         NUMBER (2))





now i have to write a script to create the xml in the
attached format, and insert this xml into the clob column

can anyone give me hint, or reference to any similar script,
or advice about how to go about it..

i have no clue

1) creating a company xml, and user xml

2) inserting into the clob columns of both the tables,
the respective xmls

any help would be greatly appreciated


  • Attachment: company.xml
    (Size: 0.88KB, Downloaded 203 times)
Re: generate xml from flat file [message #258754 is a reply to message #258750] Mon, 13 August 2007 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table to read the file and query it as you want with xml functions:
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
-----------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Regards
Michel
Re: generate xml from flat file [message #259329 is a reply to message #258754] Wed, 15 August 2007 01:22 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Do you need to first load the data from the CSV into the the database? If so I would recommend looking into SQL*Loader, I use it from the application side and it works well. As far as I know you can use it to load data. This data can be loaded into a temporary table and then you can query the temporary table like Michel showed. this can then be inserted into your tables that you need.
Re: generate xml from flat file [message #259331 is a reply to message #259329] Wed, 15 August 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, you can use an external table instead of SQL*Loader but copying the external table into a temporary table could be faster if you have multiple queries to execute on the data.

Regards
Michel
Re: generate xml from flat file [message #262695 is a reply to message #258754] Tue, 28 August 2007 01:34 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thanks
Previous Topic: retriving xml set (merged)
Next Topic: what is the datatype which supports jpeg files in oracle
Goto Forum:
  


Current Time: Wed Dec 07 04:45:54 CST 2016

Total time taken to generate the page: 0.11211 seconds