Convert xml to table using sqlloader. If a tag is missingin a record null has to be inserted in the cell. Given the xml and output.
<!-- language: lang-xml -->
<FIRCOSOFT-FOF-PROCESS>
<H>
<PRO>1494569854</PRO>
<VAL>0</VAL>
<OID>LKASJ</OID>
<JHJ>ALKSFDJ</JHJ>
<ORI>ALKSFDJ</ORI>
<ORT>AAAASFDJ</ORT>
<DSN>AAAASFDJ</DSN>
<KWS>SLKJDF</KWS>
</H>
<H>
<PRO>1494569854</PRO>
<VAL>0</VAL>
<OID>LKASJ1</OID>
<JHJ>ALKSFDJ</JHJ>
<ORI>ALKSFDJ</ORI>
<ORT>AAAASFDJ</ORT>
<DSN>AAAASFDJ</DSN>
<KWS>SLKJDF</KWS>
</H>
<H>
<PRO>1494563E854</PRO>
<VAL>0</VAL>
<OID>SSSSH1</OID>
<JHJ>ALKSFASDFDDJ</JHJ>
<ORI>ALKSFDSDJ</ORI>
<ORT>AAAASDFSFDJ</ORT>
<KWS>SLKSDFJDF</KWS>
</H>
<H>
<PRO>1494563E854</PRO>
<VAL>0</VAL>
<OID>SSSSH2</OID>
<JHJ>ALKSFASDFDDJ</JHJ>
<ORI>ALKSFDSDJ</ORI>
<ORT>AAAASDFSFDJ</ORT>
<DSN>AAAASDFSFDJ</DSN>
</H>
</FIRCOSOFT-FOF-PROCESS>
Output:
Table:
OID ORI DSN KWS
LKASJ ALKSFDJ AAAASFDJ SLKJDF
LKASJ1 ALKSFDJ AAAASFDJ SLKJDF
SSSSH1 ALKSFDSDJ NULL SLKSDFJDF
SSSSH2 ALKSFDSDJ AAAASDFSFDJ NULL
including the ctl file too:
LOAD DATA
INFILE 'c:\temp\xml.txt'
APPEND
CONTINUEIF PRESERVE (1:3) != '<H>'
INTO TABLE TBL
FIELDS TRAILING NULLCOLS
(
DUMMY1 FILLER CHAR(2000) TERMINATED BY "</TYP>",
OID CHAR(20) ENCLOSED BY "<OID>" and "</OID>",
DUMMY2 FILLER CHAR(2000) TERMINATED BY "</RID>",
ORI CHAR(20) ENCLOSED BY "<ORI>" and "</ORI>",
DSN CHAR(500) ENCLOSED BY "<DSN>" and "</DSN>",
DUMMY3 FILLER CHAR(9000) TERMINATED BY "</FEP>",
KWS CHAR(20) ENCLOSED BY "<KWS>" and "</KWS>"
)
Message was edited by: ora_1978