I asked Sean Dillon, our local XML guru, to take a look at this and here's what he had to say:
--
If you want to use the XML SQL Utility to "auto-load" your XML into relational tables, you need to transform your XML into Oracle's "canonical XML format". The XML you have listed above isn't valid in unto itself, you need an XML prolog, i.e.:
<?xml version="1.0">
Using XSLT in the database, you need to convert the XML document you store in your CLOB column:
<?xml version="1.0"?>
<Common>
<Client ClientID="98092"
ClientName="DEBORAH WALL AGENCY"
IRDNumber="26-969-212"
DOB="00/00/0000"
TactAccountNo="TACT1" />
<Client ClientID="405631"
ClientName="MR MURRAY JOHN HAYTERIAN B And THELMA M HAYTER AS TRUSTEES"
IRDNumber="13-449-187"
DOB="00/00/0000"
TactAccountNo="405631" />
<Client ClientID="417186"
ClientName="VALDA SPIERS"
IRDNumber="19-040-763"
DOB="00/00/0000"
TactAccountNo="417186" />
</Common>
...into Oracle's canonical XML format:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ClientID>"98092"</ClientID>
<ClientName>"DEBORAH WALL AGENCY"</ClientName>
<IRDNumber>="26-969-212"</IRDNumber>
<DOB>="00/00/0000"</DOB>
<TactAccountNo>"TACT1"</TactAccountNo>
</ROW>
<ROW>
<ClientID>"405631"</ClientID>
<ClientName>"MR MURRAY JOHN HAYTERIAN B And THELMA M HAYTER AS TRUSTEES"</ClientName>
<IRDNumber>="13-449-187"</IRDNumber>
<DOB>"00/00/0000"</DOB>
<TactAccountNo>="405631"</TactAccountNo>
</ROW>
<ROW>
<ClientID>"417186"</ClientID>
<ClientName>"VALDA SPIERS"</ClientName>
<IRDNumber>"19-040-763"</IRDNumber>
<DOB>"00/00/0000"</DOB>
<TactAccountNo>"417186"</TactAccountNo>
</ROW>
</ROWSET>
This can be done using an XSLT stylesheet like this:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="</code>
http://www.w3.org/1999/XSL/Transform" xmlns:fo="
http://www.w3.org/1999/XSL/Format" >
<xsl:template match="Common">
<ROWSET>
<xsl:for-each select="Client">
<ROW>
<CLIENTID><xsl:value-of select="@ClientID"/></CLIENTID>
<CLIENTNAME><xsl:value-of select="@ClientName"/></CLIENTNAME>
<IRDNUMBER><xsl:value-of select="@IRDNumber"/></IRDNUMBER>
<DOB><xsl:value-of select="@DOB"/></DOB>
<TACTACCOUNTNO><xsl:value-of select="@TactAccountNo"/></TACTACCOUNTNO>
</ROW>
</xsl:for-each>
</ROWSET>
</xsl:template>
</xsl:stylesheet>
For an example of transforming XML using XSLT inside the database, see: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1827993798549
Once the original document is converted into the canonical XML format, it can then be inserted into a table using the XML SQL Utility. For an example of doing this, see:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4061080732051
Hope that helps!
_____________________________________________________________________
Sean Dillon - Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming"
http://www.amazon.com/exec/obidos/ASIN/186100690X <code>