Skip to Main Content
  • Questions
  • how to load XML file into oracle table and using XML query to get result

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ken.

Asked: January 19, 2020 - 11:39 pm UTC

Answered by: Connor McDonald - Last updated: February 05, 2020 - 3:41 pm UTC

Category: SQL - Version: 12C v1

Viewed 100+ times

You Asked

Hi Tom,

I have XML file from Web Report. there are some error records in the file, it is difficult to find the error in XML file, I want to load this file into oracle table. then use XML query. below is sample file and expected result. This is first time using XML file, have no clue how to do this.

Thanks in advance

DocTpCd DocRefId AcctNmbr AcctNmbrTp UndocAcct ClAcct DorAcct AcctHdTpCd RCCd NoReCD IndNameType FirstName LastName Day Month Year AddrTp AddrText CityName Prov ContryCd PostCode AcctBal AcctBalTpCd
0 00 0001 001 N N N 1 CO 3 02 Wan Wan 23 09 1999 01 30 Bloor Rd Toronto Ontario CD m0m 0m0 9.29 US


    <Tag0>
        <Tag1>
            <Tag2>
                <DocSpec>
                    <DocTpCd>O</DocTpCd>
                    <DocRefId>00</DocRefId>
                </DocSpec>
                <CliAcctNmbr>
                    <AcctNmbr>0001</AcctNmbr>
                    <AcctNmbrTp>001</AcctNmbrTp>
                    <UndocAcct>N</UndocAcct>
                    <ClAcct>N</ClAcct>
                    <DorAcct>N</DorAcct>
                </CliAcctNmbr>
                <AcctH>
                    <AcctHdTpCd>1</AcctHdTpCd>
                    <Ind>
                        <ReIn>
                            <RCCd>CO</RCCd>
                            <IndIn>
       <NoReCD>3</NoReCD>
                            </IndIn>
                        </ReIn>
                        <AcctHdNM>
                            <IndNameType>02</IndNameType>
                            <FirstName>Wan</FirstName>
                            <LastName>Wan</LastName>
                        </AcctHdNM>
                        <AcctBD>
                            <Day>23</Day>
                            <Month>09</Month>
                            <Year>1999</Year>
                        </AcctBD>
                    </Ind>
                    <AcctAddr>
                        <Address>
                            <AddrTp>01</AddrTp>
                            <AddrText>30 Bloor RD</AddrText>
                            <CityName>Toronto</CityName>
                            <Prov>Ontario</Prov>
                            <ContryCd>CD</ContryCd>
                            <PostCode>m0m 0m0</PostCode>
                        </Address>
                    </AcctAddr>
                </AcctH>
                <AcctBal>9.29</AcctBal>
                <AcctBalTpCd>US</AcctBalTpCd>
            </Tag2>
   <Tag2>
                <DocSpec>
                    <DocTpCd>O</DocTpCd>
                    <DocRefId>0000001</DocRefId>
                </DocSpec>
                <CliAcctNmbr>
                    <AcctNmbr>003</AcctNmbr>
                    <AcctNmbrTp>002</AcctNmbrTp>
                    <UndocAcct>N</UndocAcct>
                    <ClAcct>N</ClAcct>
                    <DorAcct>N</DorAcct>
                </CliAcctNmbr>
                <AcctH>
                    <AcctHdTpCd>1</AcctHdTpCd>
                    <Ind>
                        <ReIn>
                            <RCCd>CO</RCCd>
                            <IndIn>
       <NoReCD>3</NoReCD>
                            </IndIn>
                        </ReIn>
                        <AcctHdNM>
                            <IndNameType>02</IndNameType>
                            <FirstName>Lin</FirstName>
                            <LastName>Wan</LastName>
                        </AcctHdNM>
                        <AcctBD>
                            <Day>23</Day>
                            <Month>09</Month>
                            <Year>1998</Year>
                        </AcctBD>
                    </Ind>
                    <AcctAddr>
                        <Address>
                            <AddrTp>01</AddrTp>
                            <AddrText>31 Bloor RD</AddrText>
                            <CityName>Toronto</CityName>
                            <Prov>Ontario</Prov>
                            <ContryCd>CD</ContryCd>
                            <PostCode>m0m 0m0</PostCode>
                        </Address>
                    </AcctAddr>

</AcctH>
<AcctBal>9.00</AcctBal>
<AcctBalTpCd>US</AcctBalTpCd>
</Tag2>
</Tag1>
</Tag0>

and we said...

and you rated our response

  (3 ratings)

Reviews

January 20, 2020 - 3:01 am UTC

Reviewer: A reader

Thank you very much for your quick reply.

I tried a example in one of the link, somehow, it gave me error.

create table ci_fa ( pk int, x xmltype );


insert into ci_fa values (1,
    xmltype('<FieldOrderNotes><completionFlags><isMeterChanged>false</isMeterChanged><isRegisterChanged>false</isRegisterChanged><isMIUChan
ged>false</isMIUChanged><isTurnedOff>true</isTurnedOff><isTurnedOn>false</isTurnedOn><isMeterRead>false</isMeterRead></completionFlags><Empl
oyee>Harris, Chris</Employee><CompletedDate>7/10/2015 10:35:47 AM</CompletedDate><Field><label>Operated C/S-FAV*</label><value>Turned On at
C/S</value></Field><Field><label>Packed Up*</label><value>Yes</value></Field><Field><label>Reading*</label><value>123356</value></Field></Fi
eldOrderNotes>'));

when running above insert statement, I got below error:

ORA-64464: XML event error
ORA-19202: Error occurred in XML processing
In line 2 of orastream:
LPX-00210: expected '=' instead of '>'


My DB version is below:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production




Chris Saxon

Followup  

January 20, 2020 - 10:57 am UTC

There are linebreaks in the XML element names. Either format the XML before inserting, or do it all in one line:

insert into ci_fa values (1,
    xmltype('<FieldOrderNotes><completionFlags>
    <isMeterChanged>false</isMeterChanged>
    <isRegisterChanged>false</isRegisterChanged>
    <isMIUChanged>false</isMIUChanged>
    <isTurnedOff>true</isTurnedOff>
    <isTurnedOn>false</isTurnedOn>
    <isMeterRead>false</isMeterRead>
    </completionFlags>
    <Employee>Harris, Chris</Employee>
    <CompletedDate>7/10/2015 10:35:47 AM</CompletedDate>
    <Field><label>Operated C/S-FAV*</label><value>Turned On at C/S</value></Field>
    <Field><label>Packed Up*</label><value>Yes</value></Field>
    <Field><label>Reading*</label><value>123356</value></Field>
</FieldOrderNotes>'));

Thanks Chris

January 20, 2020 - 8:35 pm UTC

Reviewer: A reader

@ChrisRSaxon, @Connor McDonald:
thank you, it works fine now.
one more question: I have a really big file, they way I used to insert data can only handle some data set. what's the better solution to load large XML file into ORACLE table, xmltype column.

thanks in advance
Connor McDonald

Followup  

January 21, 2020 - 1:45 am UTC

You can load XML files via SQL Loader. Example here

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/how-to-load-XML-data.html#GUID-9B6141F4-E606-4365-B1EF-2BA391A43C09

Or you could load it using DBMS_LOB via a temporary lob

create table t ( x xmltype);

declare
  dest   int := 1;
  src    int := 1;
  cs     number  := 0;
  lang   int := 0;
  warn  int := 0;
  bf     bfile;
  c      clob;
begin
  dbms_lob.createtemporary(c,true);

  bf := bfilename('TEMP', 'xmlfile.txt');
  dbms_lob.fileopen(bf, dbms_lob.file_readonly);
  dbms_lob.loadclobfromfile (
    dest_lob      => c,
    src_bfile     => bf,
    amount        => dbms_lob.lobmaxsize,
    dest_offset   => dest,
    src_offset    => src,
    bfile_csid    => cs ,
    lang_context  => lang,
    warning       => warn);
  dbms_lob.fileclose(bf);

  insert into t values (xmltype(c));
  commit;
end;
/


Ken

January 23, 2020 - 3:27 am UTC

Reviewer: A reader

Thanks Connor McDonald for you suggestion. it works perfect. I have below question. as you can see, below file one section returns multiple records (first section, it has 5 <61a> tab, it should returns 5 record, second one has 1 <61a> . it returns one record. I have no idea how to write query return multiple records for one section, others return one record.

<FinMessage xmlns="urn:swift:xsd:mtmsg.2011" xmlns:FinMessage="urn:swift:xsd:mtmsg.2011">
<Block1>
<ApplicationIdentifier>F</ApplicationIdentifier>
<ServiceIdentifier>01</ServiceIdentifier>
<LogicalTerminalAddress>IRVTDEFXAXXX</LogicalTerminalAddress>
<SessionNumber>5252</SessionNumber>
<SequenceNumber>699163</SequenceNumber>
</Block1>
<Block2>
<OutputIdentifier>O</OutputIdentifier>
<MessageType>970</MessageType>
<InputTime>1633</InputTime>
<MessageInputReference>
<Date>131101</Date>
<LTIdentifier>EBASBEBBQ</LTIdentifier>
<BranchCode>XXX</BranchCode>
<SessionNumber>1113</SessionNumber>
<ISN>070016</ISN>
</MessageInputReference>
<Date>131101</Date>
<Time>1634</Time>
<MessagePriority>N</MessagePriority>
</Block2>
<Block3>
<F108>ENS0000857017566</F108>
</Block3>
<Block4>
<Document xmlns="urn:swift:xsd:fin.970.2011" xmlns:Document="urn:swift:xsd:fin.970.2011">
<MT970>
<F20a>
<F20>ENS17566/FINAL</F20>
</F20a>
<F25a>
<F25>IRVTDEFX/EUR/131101/N</F25>
</F25a>
<F28a>
<F28C>
<StatementNumber>215</StatementNumber>
<SequenceNumber>16</SequenceNumber>
</F28C>
</F28a>
<F60a>
<F60M>
<DCMark>D</DCMark>
<Date>131101</Date>
<Currency>EUR</Currency>
<Amount>2686836,28</Amount>
</F60M>
</F60a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>40248,</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>FX5445414</ReferenceForTheAccountOwner>
<SupplementaryDetails> KREDBEBBXXXIRVTDEFXXXXN011031</SupplementaryDetails>
</F61>
</F61a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>41605,</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>FX5443846</ReferenceForTheAccountOwner>
<SupplementaryDetails> DEUTDEFFXXXIRVTDEFXXXXN011031</SupplementaryDetails>
</F61>
</F61a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>43730,</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>C13110130526301</ReferenceForTheAccountOwner>
<SupplementaryDetails> BARCGB22XXXIRVTDEFXXXXN011033</SupplementaryDetails>
</F61>
</F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>58000,</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>202</IdentificationCode>
<ReferenceForTheAccountOwner>FXT1311010000500</ReferenceForTheAccountOwner>
<ReferenceOfTheAccountServicingInstitution>FXT1311010000500</ReferenceOfTheAccountServicingInstitution>
<SupplementaryDetails> BKAUATWWXXXIRVTDEFXXXXN011116</SupplementaryDetails>
</F61>
</F61a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>59826,21</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>FX5446070</ReferenceForTheAccountOwner>
<SupplementaryDetails> CHASGB2LXXXIRVTDEFXXXXN011031</SupplementaryDetails>
</F61>
</F61a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>60309,4</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>01987HS016415</ReferenceForTheAccountOwner>
<SupplementaryDetails> EFGBGRAAXXXIRVTDEFXXXXN011458</SupplementaryDetails>
</F61>
</F61a>
<F62a>
<F62M>
<DCMark>D</DCMark>
<Date>131101</Date>
<Currency>EUR</Currency>
<Amount>3736765,9</Amount>
</F62M>
</F62a>
</MT970>
</Document>
</Block4>
<Block1>
<ApplicationIdentifier>A</ApplicationIdentifier>
<ServiceIdentifier>02</ServiceIdentifier>
<LogicalTerminalAddress>ABCDEFG</LogicalTerminalAddress>
<SessionNumber>5000</SessionNumber>
<SequenceNumber>10000</SequenceNumber>
</Block1>
<Block2>
<OutputIdentifier>O</OutputIdentifier>
<MessageType>180</MessageType>
<InputTime>123</InputTime>
<MessageInputReference>
<Date>1234</Date>
<LTIdentifier>ABCDABCD</LTIdentifier>
<BranchCode>XXX</BranchCode>
<SessionNumber>1113</SessionNumber>
<ISN>0258</ISN>
</MessageInputReference>
<Date>2010</Date>
<Time>1634</Time>
<MessagePriority>Y</MessagePriority>
</Block2>
<Block3>
<F108>BBB0000857017566</F108>
</Block3>
<Block4>
<Document xmlns="urn:swift:xsd:fin.970.2011" xmlns:Document="urn:swift:xsd:fin.970.2011">
<MT970>
<F20a>
<F20>FINAL</F20>
</F20a>
<F25a>
<F25>AXSDS/EUR/131101/N</F25>
</F25a>
<F28a>
<F28C>
<StatementNumber>987</StatementNumber>
<SequenceNumber>16</SequenceNumber>
</F28C>
</F28a>
<F60a>
<F60M>
<DCMark>D</DCMark>
<Date>131101</Date>
<Currency>EUR</Currency>
<Amount>2686836,28</Amount>
</F60M>
</F60a>
<F61a>
<F61>
<ValueDate>131101</ValueDate>
<DebitCreditMark>D</DebitCreditMark>
<Amount>40248,</Amount>
<TransactionType>S</TransactionType>
<IdentificationCode>103</IdentificationCode>
<ReferenceForTheAccountOwner>BADDADS</ReferenceForTheAccountOwner>
<SupplementaryDetails>BACKDFDSA</SupplementaryDetails>
</F61>
</F61a>
<F62a>
<F62M>
<DCMark>D</DCMark>
<Date>2010101</Date>
<Currency>EUR</Currency>
<Amount>3736765,9</Amount>
</F62M>
</F62a>
</MT970>
</Document>
</Block4>
</FinMessage>
Chris Saxon

Followup  

February 05, 2020 - 3:41 pm UTC

XMLTable will automatically generate a row for each element in the group. If you have multiple levels, you can chain XMLTable calls:

with rws as (
  select xmltype ( '
<xml>
  <block>
    <group>1</group>
    <f61>1</f61>
    <f61>2</f61>
    <f61>3</f61>
  </block>
  <block>
    <group>2</group>
    <f61>1</f61>
  </block>
</xml>' ) x
  from   dual
)
  select grp, val 
  from   rws, xmltable (
    '/xml/block'
    passing x 
    columns 
      grp int path 'group',
      f61 xmltype path 'f61'
  ) x1 , xmltable (
    '/f61'
    passing x1.f61
    columns 
      val int path '.'
  );
  
GRP   VAL   
     1      1 
     1      2 
     1      3 
     2      1

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.