Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Load xml file to Oracle table

User_JOHIRSep 7 2018 — edited Sep 12 2018

Experts,

This is duplicate thread for my previous threads.  My oracle version is 12.2.0.1.0

I am new to XML. Am not sure, how to create oracle tables based on given XML file. So would need experts advise, based on the below given XML, please help me to create oracle table and load the data from XML to Oracle tables.

Below is the source table for .xml
=================================

CREATE TABLE XML_TAB_LD
(
XML_Id NUMBER, --( PRIMARY KEY)
XML_DC XMLTYPE,
FILE_PATH VARCHAR2(200), --(UNIQUE KEY)
FILE_NAME VARCHAR2(100), --(UNIQUE KEY)
FILE_DATE DATE,
FILE_TYPE VARCHAR2(100),
CREATED_DT TIMESTAMP,
LAST_UPDATED_DT TIMESTAMP
);
cONSTRAINTS :
XML_IN_PK(XML_Id )
XML_IN_UK (FILE_PATH ,FILE_NAME )

Below is my XML file.

<?xml version="1.0" encoding="UTF-8"?>
-<prdhrcy xsi:noNamespaceSchemaLocation="http://www.abc.com/EMPID/namespace/doctypes/pubstruct" version="2.1" uri="x-wc://e29f9e65f5ad7794:wt.part.artMaster:PEGAGRP1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PEGAGRP" id="wt.part.WTPartMaster_38941461367" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
-<Metadata id="TKPart_38942985377" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PEGAGRP">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Group</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>PHGRP1</Value>
</Property>
-<Property entry="objNumber">
<Value>PHGRP1</Value>
</Property>
-<Property entry="thePersistInfo.updateStamp">
<Value>2018-08-14 13:51:24.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Group</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="thePersistInfo.modifyStamp">
<Value>2018-08-14 13:51:24.0</Value>
</Property>
</Metadata>
-<Content uri="x-wc://8685521b7f2d108:wt.part.WTPartMaster:P1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PClass" id="wt.part.WTPartMaster_38942985310">
-<Metadata id="TKPart_38942985397" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PClass">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Sample Class</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>P1</Value>
</Property>
-<Property entry="objNumber">
<Value>P1</Value>
</Property>
-<Property entry="thePersistInfo.updateStamp">
<Value>2018-08-14 13:50:41.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Sample Class</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="thePersistInfo.modifyStamp">
<Value>2018-08-14 13:50:41.0</Value>
</Property>
</Metadata>
-<Content uri="x-wc://6e82c2a3db06c287:wt.part.WTPartMaster:PHM1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PModel" id="wt.part.WTPartMaster_38942985348">
-<Metadata id="TKPart_38942985351" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PModel">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Sample Model</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>PHM1</Value>
</Property>
-<Property entry="objNumber">
<Value>PHM1</Value>
</Property>
-<Property entry="thePersistInfo.updateStamp">
<Value>2018-08-14 13:48:21.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Sample Model</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="thePersistInfo.modifyStamp">
<Value>2018-08-14 13:48:21.0</Value>
</Property>
</Metadata>
</Content>
</Content>
</prdhrcy><?xml version="1.0" encoding="UTF-8"?>
-<prdhrcy xsi:noNamespaceSchemaLocation="http://www.abc.com/EMPID/namespace/doctypes/pubstruct" version="2.1" uri="x-wc://e29f9e65f5ad7794:wt.part.artMaster:PEGAGRP1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PEGAGRP" id="wt.part.WTPartMaster_38941461367" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
-<Metadata id="TKPart_38942985377" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PEGAGRP">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Group</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>PHGRP1</Value>
</Property>
-<Property entry="objNumber">
<Value>PHGRP1</Value>
</Property>
-<Property entry="persinfo.updateStamp">
<Value>2018-08-14 13:51:24.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Group</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="persinfo.modifyStamp">
<Value>2018-08-14 13:51:24.0</Value>
</Property>
</Metadata>
-<Content uri="x-wc://8685521b7f2d108:wt.part.WTPartMaster:P1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PClass" id="wt.part.WTPartMaster_38942985310">
-<Metadata id="TKPart_38942985397" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PClass">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Sample Class</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>P1</Value>
</Property>
-<Property entry="objNumber">
<Value>P1</Value>
</Property>
-<Property entry="persinfo.updateStamp">
<Value>2018-08-14 13:50:41.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Sample Class</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="persinfo.modifyStamp">
<Value>2018-08-14 13:50:41.0</Value>
</Property>
</Metadata>
-<Content uri="x-wc://6e82c2a3db06c287:wt.part.WTPartMaster:PHM1" type="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PModel" id="wt.part.WTPartMaster_38942985348">
-<Metadata id="TKPart_38942985351" source="ext.dermo.part.TKPart|com.rams.ccwc.rams.PBase|com.rams.ccwc.rams.PModel">
-<Property entry="orgName">
<Value>rams</Value>
</Property>
-<Property entry="objName">
<Value>PH Sample Model</Value>
</Property>
-<Property entry="defaultUnit">
<Value>ea</Value>
</Property>
-<Property entry="number">
<Value>PHM1</Value>
</Property>
-<Property entry="objNumber">
<Value>PHM1</Value>
</Property>
-<Property entry="persinfo.updateStamp">
<Value>2018-08-14 13:48:21.0</Value>
</Property>
-<Property entry="servicekit">
<Value>false</Value>
</Property>
-<Property entry="name">
<Value>PH Sample Model</Value>
</Property>
-<Property entry="org.id">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="serviceable">
<Value>true</Value>
</Property>
-<Property entry="orgref">
<Value>WCTYPE|wt.org.WHO~~WCP|433257403|123724</Value>
</Property>
-<Property entry="persinfo.modifyStamp">
<Value>2018-08-14 13:48:21.0</Value>
</Property>
</Metadata>
</Content>
</Content>
</prdhrcy>
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 10 2018
Added on Sep 7 2018
#performance-availability, #xml-db
29 comments
4,599 views