How do you load data from XML file to a ORACLE table?
Answer
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;
|
More XML Links
Answer - XML, Extensible Markup Language, is an open, text
based markup language that provides structural and semantic information to
data...............
Answer - The primary XML index is a B+tree and is useful
because the optimizer creates a plan for the entire query. ..............
Answer - SQL Server 2000 provides the facility to retrieve data
in the form of XML with the help of the FOR XML clause appended to the end of a
SELECT statement. ..............
|
|