Re: xml->dml ?
- From: Chris Stephens <cstephens16@xxxxxxxxx>
- To: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
- Date: Wed, 31 Aug 2005 08:04:56 -0400
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)
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
