Im trying to figure out the best was to parse an external XML file in Oracle.
We have a number of XML files titled "XMLFileToImport_YYYYMMDD01234" with the following format.
<Employee>
<Person>
<ID>0123456789</ID>
<Identifier>
<AssignedID>0000001</AssignedID>
<AssignedCode>New York</AssignedCode>
<AssignedCityCode>Gotham</AssignedCityCode>
</Identifier>
<RecipientAssignedID>202301346</RecipientAssignedID>
<Birth>
<BirthDate>1963-02-19</BirthDate>
</Birth>
<Name>Bruce Wayne</Name>
<Gender>
<GenderCode>Male</GenderCode>
</Gender>
</Person>
</Employee>
We have to be able to automate (great to run every 4ish hours) the import of these XML files for the names will change as well.
I am not finding any specific clear examples on how to go about this.
Do I have to import the XML as a CLOB to a holding table then grab and the CLOB and parse the data using XMLTABLE or XMLTAG or XMLATTRIBUTE?
Do I have to import the XML at all? Can I/should I run a SQL script that parses the XML without even bringing it into Oracle?
I believe the preference is to store the XML data in Oracle as a CLOB so we can have a history in the DB.
Any examples/Advice/suggestions of how to go about this would be extremely helpful!