Home » Developer & Programmer » JDeveloper, Java & XML » Loading of xml into oracle relational table
icon2.gif  Loading of xml into oracle relational table [message #159560] Mon, 20 February 2006 05:17 Go to next message
jayukanna
Messages: 5
Registered: February 2006
Location: INDIA
Junior Member
This is my xsd
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
<!-- <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> -->
<xs:element name="Bill">
<xs:complexType>
<xs:sequence>
<xs:element ref="InvCtlN"/>
<xs:element ref="MedBU"/>
<xs:element ref="Lineitem" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="InvCtlN">
<xs:simpleType>
<xs:restriction base="xs:byte">
<xs:enumeration value="1"/>
<xs:enumeration value="2"/>
<xs:enumeration value="3"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LineCode">
<xs:simpleType>
<xs:restriction base="xs:int">
<xs:enumeration value="99214"/>
<xs:enumeration value="99215"/>
<xs:enumeration value="99216"/>
<xs:enumeration value="99217"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Lineitem">
<xs:complexType>
<xs:sequence>
<xs:element ref="Type"/>
<xs:element ref="LineCode"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MedBU">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="ABC"/>
<xs:enumeration value="DEF"/>
<xs:enumeration value="HJK"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SampleFile">
<xs:complexType>
<xs:sequence>
<xs:element ref="Bill" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Type">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="PR2"/>
<xs:enumeration value="PR3"/>
<xs:enumeration value="PRO"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:schema>

This is my xml file.
<SampleFile xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://HDHDW0035:8080/MYFILE.xsd">
<Bill>
<InvCtlN>1</InvCtlN>
<MedBU>ABC</MedBU>
<Lineitem>
<Type>P1</Type>
<LineCode>99214</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>2</InvCtlN>
<MedBU>DEF</MedBU>
<Lineitem>
<Type>P2</Type>
<LineCode>99215</LineCode>
</Lineitem>
<Lineitem>
<Type>P3</Type>
<LineCode>99216</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>3</InvCtlN>
<MedBU>HJK</MedBU>
<Lineitem>
<Type>P4</Type>
<LineCode>99217</LineCode>
</Lineitem>
</Bill>
</SampleFile>


Iam able to register the xsd in the oracle database.

After I insert the xsd, I want to move the data to 2 tables in oracle

Master_table
InvCtlN MedBu
1 ABC
2 DEF
3 HJK

Detail_Table
InvCtlN Type LineCode
1 P1 99214
2 P2 99215
2 P3 99216
3 P4 99217

Can someone please help me with the query part?
How should my query be designed to obtain the above result from the xml?

Thanks in advance,
Jay
Re: Loading of xml into oracle relational table [message #159733 is a reply to message #159560] Tue, 21 February 2006 02:30 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello there.

Probably the easiest way is to use xpath extracts to process this
xml document :
/*
CREATE TABLE master_table ( invctln  NUMBER,  medbu   VARCHAR2(10) )
/

CREATE TABLE detail_table ( invctln  NUMBER, type  VARCHAR2(10), linecode   NUMBER )
/
*/
DECLARE
  x  XMLTYPE := XMLTYPE('
<SampleFile xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://HDHDW0035:8080/MYFILE.xsd">
<Bill>
<InvCtlN>1</InvCtlN>
<MedBU>ABC</MedBU>
<Lineitem>
<Type>P1</Type>
<LineCode>99214</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>2</InvCtlN>
<MedBU>DEF</MedBU>
<Lineitem>
<Type>P2</Type>
<LineCode>99215</LineCode>
</Lineitem>
<Lineitem>
<Type>P3</Type>
<LineCode>99216</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>3</InvCtlN>
<MedBU>HJK</MedBU>
<Lineitem>
<Type>P4</Type>
<LineCode>99217</LineCode>
</Lineitem>
</Bill>
</SampleFile>');
BEGIN
  FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/Bill/InvCtlN') invctln,
                    EXTRACTVALUE(VALUE(t), '/Bill/MedBU') medbu,
                    EXTRACT(VALUE(t), '/Bill/Lineitem') lineitem_xml
               FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/SampleFile/Bill'))) t )
  LOOP
    INSERT INTO master_table ( invctln, medbu )
    VALUES ( i.invctln, i.medbu );

    FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/Lineitem/Type') type,
                      EXTRACTVALUE(VALUE(t), '/Lineitem/LineCode') linecode
                 FROM TABLE(XMLSEQUENCE(EXTRACT(i.lineitem_xml, '/Lineitem'))) t )
    LOOP
      INSERT INTO detail_table ( invctln, type, linecode )
      VALUES ( i.invctln, j.type, j.linecode );
    END LOOP;
  END LOOP;
END;
/

Rgds
icon14.gif  Re: Loading of xml into oracle relational table [message #159737 is a reply to message #159733] Tue, 21 February 2006 02:48 Go to previous messageGo to next message
jayukanna
Messages: 5
Registered: February 2006
Location: INDIA
Junior Member
Thanks alot. That was a very useful response.
icon12.gif  Re: Loading of xml into oracle relational table [message #160114 is a reply to message #159737] Thu, 23 February 2006 06:00 Go to previous messageGo to next message
jayukanna
Messages: 5
Registered: February 2006
Location: INDIA
Junior Member
Hi mchadder!

I have problems when I try to insert my xml into the database.

Iam able to register my schema. My schema validates against my XML successfully.
But when I try to insert into the oracle created table I get an error
ora-190007 - Schema and Element do not match.

INSERT INTO "MN"
VALUES
(
xmltype
(
getFileContent('name_1.xml','XMLSAMP')
)
);

What can be the possible reasons for such an error when xml file and xsd are "matching"?
You can try with the example I had given the previous posts. I got an error while trying to insert the xml file into the database.


Regards,
Jayanthi

Re: Loading of xml into oracle relational table [message #160182 is a reply to message #159560] Thu, 23 February 2006 13:46 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Well, there's no doubt that your XML document is invalid against the XSD, since the enumeration of the Type element is either PR2, PR3 or PRO, yet your XML document defines values such as P1.

But, that error arises when the definition of the XMLTYPE column which has been created in your table doesn't match the XML document / XSD, you'd have to post your CREATE TABLE command for "MN", but here's the correct setup you need (with caveats, of course, that i've changed the data to be valid against the XSD) :
SQL> BEGIN
  2  dbms_xmlschema.registerSchema(
  3        schemaurl => 'http://HDHDW0035:8080/MYFILE.xsd',
  4        schemadoc => '<?xml version="1.0" encoding="ISO-8859-1" ?>
  5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">

  6  <!-- <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> -->

  7  <xs:element name="Bill">
  8  <xs:complexType>
  9  <xs:sequence>
 10  <xs:element ref="InvCtlN"/>
 11  <xs:element ref="MedBU"/>
 12  <xs:element ref="Lineitem" maxOccurs="unbounded"/>
 13  </xs:sequence>
 14  </xs:complexType>
 15  </xs:element>
 16  <xs:element name="InvCtlN">
 17  <xs:simpleType>
 18  <xs:restriction base="xs:byte">
 19  <xs:enumeration value="1"/>
 20  <xs:enumeration value="2"/>
 21  <xs:enumeration value="3"/>
 22  </xs:restriction>
 23  </xs:simpleType>
 24  </xs:element>
 25  <xs:element name="LineCode">
 26  <xs:simpleType>
 27  <xs:restriction base="xs:int">
 28  <xs:enumeration value="99214"/>
 29  <xs:enumeration value="99215"/>
 30  <xs:enumeration value="99216"/>
 31  <xs:enumeration value="99217"/>
 32  </xs:restriction>
 33  </xs:simpleType>
 34  </xs:element>
 35  <xs:element name="Lineitem">
 36  <xs:complexType>
 37  <xs:sequence>
 38  <xs:element ref="Type"/>
 39  <xs:element ref="LineCode"/>
 40  </xs:sequence>
 41  </xs:complexType>
 42  </xs:element>
 43  <xs:element name="MedBU">
 44  <xs:simpleType>
 45  <xs:restriction base="xs:string">
 46  <xs:enumeration value="ABC"/>
 47  <xs:enumeration value="DEF"/>
 48  <xs:enumeration value="HJK"/>
 49  </xs:restriction>
 50  </xs:simpleType>
 51  </xs:element>
 52  <xs:element name="SampleFile">
 53  <xs:complexType>
 54  <xs:sequence>
 55  <xs:element ref="Bill" maxOccurs="unbounded"/>
 56  </xs:sequence>
 57  </xs:complexType>
 58  </xs:element>
 59  <xs:element name="Type">
 60  <xs:simpleType>
 61  <xs:restriction base="xs:string">
 62  <xs:enumeration value="PR2"/>
 63  <xs:enumeration value="PR3"/>
 64  <xs:enumeration value="PRO"/>
 65  </xs:restriction>
 66  </xs:simpleType>
 67  </xs:element>
 68  </xs:schema>',
 69  local => FALSE);
 70  END;
 71  /

PL/SQL procedure successfully completed.

SQL> CREATE TABLE test_xml (
  2    test_xml XMLTYPE
  3  )
  4     XMLTYPE test_xml STORE AS OBJECT RELATIONAL
  5        XMLSCHEMA "http://HDHDW0035:8080/MYFILE.xsd"
  6        ELEMENT "SampleFile"
  7  /

Table created.

SQL> INSERT INTO test_xml ( test_xml )
  2  VALUES
  3  (
  4  XMLTYPE('<SampleFile
  5                   xmlns:xdb="http://xmlns.oracle.com/xdb"
  6                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  7                   xsi:noNamespaceSchemaLocation="http://HDHDW0035:8080/MYFILE.xsd">
  8  <Bill>
  9  <InvCtlN>1</InvCtlN>
 10  <MedBU>ABC</MedBU>
 11  <Lineitem>
 12  <Type>PR2</Type>
 13  <LineCode>99214</LineCode>
 14  </Lineitem>
 15  </Bill>
 16  <Bill>
 17  <InvCtlN>2</InvCtlN>
 18  <MedBU>DEF</MedBU>
 19  <Lineitem>
 20  <Type>PR2</Type>
 21  <LineCode>99215</LineCode>
 22  </Lineitem>
 23  <Lineitem>
 24  <Type>PR3</Type>
 25  <LineCode>99216</LineCode>
 26  </Lineitem>
 27  </Bill>
 28  <Bill>
 29  <InvCtlN>3</InvCtlN>
 30  <MedBU>HJK</MedBU>
 31  <Lineitem>
 32  <Type>PRO</Type>
 33  <LineCode>99217</LineCode>
 34  </Lineitem>
 35  </Bill>
 36  </SampleFile>')
 37  )
 38  /

1 row created.

Rgds

Previous Topic: XMLParser hanging
Next Topic: XML (9i): How to replace the ROW node with custom node with attributes?
Goto Forum:
  


Current Time: Thu Mar 28 13:11:37 CDT 2024