Re: xml->dml ?

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Wed, 31 Aug 2005 08:04:56 -0400
Message-ID: <d95e860705083105047e7ee56a@mail.gmail.com>

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_at_trivadis.com> 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_at_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_at_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

Received on Wed Aug 31 2005 - 07:07:37 CDT