A reader, January 20, 2020 - 3:01 am UTC
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
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
A reader, January 20, 2020 - 8:35 pm UTC
@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
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
A reader, January 23, 2020 - 3:27 am UTC
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>
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