How do you load data from XML file to a ORACLE table?
You need to first create a table in oracle that matches with the fields of the XML data.
So to get the XMl into the table, you can create a generic procedure that moves an XML document into a table by converting the elements to Oracle Canonical format.
Oracle Canonical format is as follows:<ROWSET>
<ROW>
<column_name_1 />
.
.
</ROW>
<ROW>
<column_name_1 />
.
.
</ROW>
.
.
</ROWSET>
ROW is used for the table names
ROWSET is used for the XML document
You can get the XML into the canonical form using XSL:<?xml version="1.0"?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:template match="/">
<ROWSET>
<ROW>
<Attribute_name>
<xsl:value-of select="Table_name/Attribute_name" />
</Attribute_name>
< /ROW>
</ROWSET>
</xsl:template>
</xsl:stylesheet>
To transform the XML document into the canonical form, you can write a procedure. Make sure you include the line below in your procedure code:
v_rows := DBMS_XMLStore.insertXML(v_context, XMLType.transform(p_xml_in, p_xsl_in));
The only remaining step is calling your procedure:DECLARE
v_xml XMLType := XMLType( YOUR XML Document );
v_xsl XMLType := XMLType( YOUR XSL Document );
BEGIN
procedure_name(v_xml, v_xsl, 'table_name');
END;
