Re: xml->dml ?

what i'm actually looking for is an automatic way to generate the
insert statements from the xsd to use against the xml.

so and example would be:
in the xsd:

<xs:element name="MonitoringPlan" sql:relation="MP_MonitoringPlan"
sql:key-fields="MP_PK" sql:overflow-field="OverflowColumn" >
    <xs:complexType>
        <xs:sequence>
        <xs:element name="MonitoringPlanIDKey"  
sql:field="MonitoringPlanIDKey"/>
        <xs:element name="FacilityIDKey"  sql:field="FacilityIDKey"/>
        <xs:element name="ORISCode" sql:field="ORISPL"/>
        <xs:element name="ConfigurationTypeCode"  
sql:field="ConfigurationTypeCode"/>
        <xs:element name="MonitoringPlanName"  sql:field="MonitoringPlanName"/>
        <xs:element name="MonitoringPlanDescription"
sql:field="MonitoringPlanDescription"/>
        <xs:element name="StartDate"  sql:field="StartDate"/>
        <xs:element name="EndDate"  sql:field="EndDate"/>
        <xs:element name="UserID"  sql:field="UserID"/>
        <xs:element name="AddDate"  sql:field="AddDate"/>
        <xs:element name="UpdateDate"  sql:field="UpdateDate"/>
        <xs:element ref="MonitoringPlanCommentData" />
        <xs:element ref="StackPipeData"/>
        <xs:element ref="UnitData"/>
        <xs:element ref="MonitoringPlanConfigurationData"/>
        </xs:sequence>
        </xs:complexType>
        </xs:element>

would map to:

   INSERT INTO MP_MonitoringPlan (MP_PK,ACTIVITY_ID, MonitoringPlanIDKey,  
          FacilityIDKey, ORISPL, ConfigurationTypeCode, MonitoringPlanName, 
MonitoringPlanDescription,StartDate,EndDate,
 UserID,AddDate,UpdateDate)
  (SELECT 2,'DDD',extractvalue(XML_DOC, '/MonitoringPlan/MonitoringPlanIDKey'),
 extractvalue(XML_DOC, '/MonitoringPlan/FacilityIDKey'),
 extractvalue(XML_DOC, '/MonitoringPlan/ORISCode'),
 extractvalue(XML_DOC, '/MonitoringPlan/ConfigurationTypeCode'),
 extractvalue(XML_DOC, '/MonitoringPlan/MonitoringPlanName'),
 extractvalue(XML_DOC, '/MonitoringPlan/MonitoringPlanDescription'),
 to_date(extractvalue(XML_DOC, '/MonitoringPlan/StartDate'),'YYYY-MM-DD'),
 to_date(extractvalue(XML_DOC, '/MonitoringPlan/EndDate'),'YYYY-MM-DD'),
 extractvalue(XML_DOC, '/MonitoringPlan/UserID'),
 to_date(extractvalue(XML_DOC, '/MonitoringPlan/AddDate'),'YYYY-MM-DD HH24:MI'),
 to_date(extractvalue(XML_DOC,
'/MonitoringPlan/UpdateDate'),'YYYY-MM-DD HH24:MI')
  FROM xml_repo);

On 8/31/05, Christian Antognini <Christian.Antognini@xxxxxxxxxxxx> wrote:
> Hi Chris
> 
> >i just recieved an xsd from a .net developer that tells me how to
> >create a series of dml statements to extract data out of an xml
> >document through extractvalue()
> >
> >i'd hate to manually do this.  ...automating that myself is above my
> >abilities at this point (it seems like a regular expression problem
> >but i won't pretend to have a clue).  ...i'v been asking lots of
> >questions here lately so i guess another one (or two) won't hurt:
> >
> >is this something that occurs frequently when xml is to be loaded into
> >relational tables?  are there tools/scripts that automate this?
> 
> If the mapping is simple, XSU (a command line utility provided with XDK) 
> could be a solution.
> Here an example on how you can load a XML file...
> 
> -- my input file
> 
> oracle@trantor:/tmp/ [A1020] cat dept.xml
> <?xml version = '1.0'?>
> <ROWSET>
>    <ROW num="1">
>       <DEPTNO>10</DEPTNO>
>       <DNAME>ACCOUNTING</DNAME>
>       <LOC>NEW YORK</LOC>
>    </ROW>
>    <ROW num="2">
>       <DEPTNO>20</DEPTNO>
>       <DNAME>RESEARCH</DNAME>
>       <LOC>DALLAS</LOC>
>    </ROW>
>    <ROW num="3">
>       <DEPTNO>30</DEPTNO>
>       <DNAME>SALES</DNAME>
>       <LOC>CHICAGO</LOC>
>    </ROW>
>    <ROW num="4">
>       <DEPTNO>40</DEPTNO>
>       <DNAME>OPERATIONS</DNAME>
>       <LOC>BOSTON</LOC>
>    </ROW>
> </ROWSET>
> 
> -- set environment
> 
> export 
> CLASSPATH=${ORACLE_HOME}/lib/xsu12.jar:${ORACLE_HOME}/lib/xmlparserv2.jar:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar:${ORACLE_HOME}/rdbms/jlib/xdb.jar
> 
> -- run XSU
> 
> oracle@trantor:/tmp/ [A1020] java OracleXML putXML -user scott/tiger 
> -fileName dept.xml dept                         successfully inserted 4 rows 
> into dept
> 
> 
> For more information refer to "Oracle XML Developer's Kit Programmer's Guide".
> 
> 
> HTH
> Chris
> 
> New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com
> Italiano: Lugano (24-Nov)
> Français: Genève (17-Nov)
> Deutsch: Zürich (11-Oct), Hamburg (13-Oct), München (20-Oct), Basel (25-Oct),
>          Frankfurt (27-Oct), Bern (8-Nov), Düsseldorf (23-Nov), Stuttgart 
> (13-Dec)
>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: