How to store/query an XML file in Oracle 12.2

40 Views Asked by At

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!

0

There are 0 best solutions below