Home » RDBMS Server » Server Utilities » Load .XML file using SQL*Loader (Database - Oracle 9i, OS - Win2003 Server)
Load .XML file using SQL*Loader [message #384912] Thu, 05 February 2009 13:51 Go to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Hi,

I am looking for a solution to load .XML file in table using SQL*Loader. I have googled this but didn’t find any proper solutions.

My XML data is like (single record)–

- <Documents>
<Version>20011011</Version>
- <Document>
<TransType>Amendment</TransType>
<FilingMethod Method="Electronic" />
- <AmendmentActionLoop>
<AmendmentAction Action="NOAction" />
</AmendmentActionLoop>
- <AmendmentTypeLoop>
<AmendmentType Type="Continuation" />
</AmendmentTypeLoop>
<OriginalFileNumber>11111</OriginalFileNumber>
<LapseDate>20140624</LapseDate>
<FileNumber>111111</FileNumber>
<FileDate>20090123</FileDate>
<FileTime>00:16</FileTime>
<FilingOffice>WA DOL</FilingOffice>
<ActionCode />
<AltNameDesignation AltName="NOAltName" />
<AltFilingType AltType="NOAltType" />
<MiscInfo />
- <CurrentNames>
- <CurrentName>
- <Names>
<OrganizationName />
</Names>
</CurrentName>
</CurrentNames>
- <Debtors>
- <DebtorName>
- <Names>
<OrganizationName />
<MailAddress />
<City />
<State />
<PostalCode />
<County />
<Country />
<TaxID />
<OrganizationalType />
<OrganizationalJuris />
<OrganizationalID />
<Mark />
</Names>
<DebtorAltCapacity AltCapacity="NOAltCapacity" />
</DebtorName>
</Debtors>
- <Secured>
- <Names>
<OrganizationName />
<MailAddress />
<City />
<State />
<PostalCode />
<County />
<Country />
<TaxID />
<OrganizationalType />
<OrganizationalJuris />
<OrganizationalID />
<Mark />
</Names>
</Secured>
<Collateral />
- <AuthorizingParty>
- <AuthSecuredParty>
<OrganizationName>Bank of America, N.A.</OrganizationName>
</AuthSecuredParty>
- <AuthDebtor>
<OrganizationName />
</AuthDebtor>
</AuthorizingParty>
</Document>

From above XML I want to load FileNumber in a separate column and rest all the details in other column. Is it doable???

Thankx in advance,
Rahul.
Re: Load .XML file using SQL*Loader [message #384913 is a reply to message #384912] Thu, 05 February 2009 13:55 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is easy with an external table or using queries like in:
http://www.orafaq.com/forum/t/139289/102589/
http://www.orafaq.com/forum/m/380259/102589/#msg_380259

Regards
Michel
Previous Topic: converting oracle.ora file
Next Topic: SQL Loader with record Delimiter Issue
Goto Forum:
  


Current Time: Wed Dec 07 04:52:27 CST 2016

Total time taken to generate the page: 0.17799 seconds