Load XML file oracle Table
Posted: Fri Sep 28, 2018 5:13 am
Hi Experts,
I have created a XML table as follows.
vCREATE TABLE XML_TAB_LD
(
XML_Id NUMBER,
XML_DC XMLTYPE, -- this columns contails all the XML file contents
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
);
Have XML file as follows
Based the above XML file , I would like to retrieve, Parent,Child, grand Child etc.. into one table. The table should display the hierarchy once we query it. I am not created any Target table as am not sure what are the required columns for above XML and we need to add with data type. Please suggest me to create a Target a Target table and load the above XML.
Thanks,
Kris.
I have created a XML table as follows.
vCREATE TABLE XML_TAB_LD
(
XML_Id NUMBER,
XML_DC XMLTYPE, -- this columns contails all the XML file contents
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
);
Have XML file as follows
Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<DUMP xmlns:xs="http://www.w3.org/2001/XMLSchema" xml:lang="en-US">
<title>IS-TEST</title>
<!--Includes all Metadata sources.--><DMPmeta>
<DMPprojectinfo encryption="no" projectname="vegas - Scroll comp"/>
<data name="contenttype">is</data>
<data name="volumename"/>
<data name="volumeid"/>
<data name="vendor"/>
</DMPmeta>
<languageset locale="en-US">
<topicref id="wt.part.WTPartMaster_38895837100" format="xml" navtitle="IS-TEST"
locktitle="yes">
<topicmeta>
<metadata id="PDMLinkProduct_8640959636">
<category>wt.pdmlink.PDMLinkProduct</category>
<data name="name">vegas - Scroll comp</data>
<data name="orgName">islan</data>
<data name="description">vegas - Scroll comp - Climate</data>
<data name="owner">Administrator</data>
<data name="createdDate">2010-12-11 13:09:51.0</data>
<data name="createdBy">Administrator</data>
<data name="preference.com.ptc.arbortext.charls.siscore.translation.SourceLanguage">en</data>
</metadata>
<metadata>
<category>SIM</category>
<data name="publishDate">2018-08-14 14:01:29:177</data>
</metadata>
<metadata id="WTPart_38896272089">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsRoot</category>
<data name="isPrimaryASPSRoot">true</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042587</data>
<data name="objNumber">0000042587</data>
<data name="sis.group.contentLocation">/vegas - Scroll comp</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 22:07:08.0</data>
<data name="orgName">islan</data>
<data name="objName">IS-TEST</data>
<data name="thePersistInfo.updateStamp">2018-07-18 22:07:08.0</data>
<data name="servicekit">false</data>
<data name="isTemplate">false</data>
<data name="name" transidref="3674a01d9fa6789a" xml:lang="en-US">IS-TEST</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
</topicmeta>
<topicref id="wt.part.WTPartMaster_38896425919" format="xml"
navtitle="Mode Number Description"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38896425936">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsGroup</category>
<data name="orgName">islan</data>
<data name="objName">Mode Number Description</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042592</data>
<data name="objNumber">0000042592</data>
<data name="thePersistInfo.updateStamp">2018-07-18 22:07:03.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="sis.group.contentLocation">/vegas - Scroll comp</data>
<data name="name" transidref="d6f749be144e2264" xml:lang="en-US">Mode Number Description</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 22:07:03.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
<data name="ServiceEffectivity">(AND (PRODUCT 'PHM1') (INSTANCE '222222'))</data>
</metadata>
<metadata id="WTPartUsageLink_38896425932">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">62</data>
<data name="thePersistInfo.updateStamp">2018-07-18 21:52:34.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 21:52:34.0</data>
</metadata>
</topicmeta>
<topicref id="wt.part.WTPartMaster_38896425962" href="x-wc://file=0005636428.png"
copy-to="com.islan.ccwc.islan.DynamicDocument/38895848265.png"
scope="local"
format="png"
navtitle="55533-2-2-9"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38896425966">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.Content|com.ptc.sis.ContentRef|com.ptc.sis.IllustrationRef</category>
<data name="orgName">islan</data>
<data name="objName">55533-2-2-9</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042593</data>
<data name="objNumber">0000042593</data>
<data name="thePersistInfo.updateStamp">2018-07-18 22:06:43.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="name" transidref="c23f3633a10a451d" xml:lang="en-US">55533-2-2-9</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 22:06:43.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
<metadata id="WTPartUsageLink_38896425975">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">1000</data>
<data name="thePersistInfo.updateStamp">2018-07-18 22:06:43.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 22:06:43.0</data>
</metadata>
<metadata id="SVSdoc_38895848265">
<category>wt.epm.SVSdoc|com.islan.ccwc.islan.DynamicDocument</category>
<data name="orgName">islan</data>
<data name="revisionNumber">0</data>
<data name="SIM.lastUpdated">2018-07-18 13:58:20.0</data>
<data name="docType">PUB_GRAPHIC</data>
<data name="missingDependents">false</data>
<data name="type">Dynamic Document</data>
<data name="typeDisplayName">Dynamic Document</data>
<data name="versionIdentifer.versionId">A</data>
<data name="Name" transidref="c23f3633a10a451d" xml:lang="en-US">55533-2-2-9</data>
<data name="number">0005636428</data>
<data name="authoringApplication">GRAPHICSEDITOR</data>
<data name="Number">0000042593</data>
<data name="SIM.lastUpdatedSrc">2018-07-18 13:58:20.0</data>
<data name="name">55533-2-2-9</data>
<data name="iteration">0</data>
<data name="SIM.lastUpdatedMeta">2018-07-18 22:06:43.0</data>
<data name="PTC_DD_LANGUAGE">en-US</data>
<data name="thePersistInfo.createStamp">2018-07-18 13:58:18.0</data>
<data name="derived">false</data>
<data name="CADName">0005636428.png</data>
<data name="placeHolder">false</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 13:58:18.0</data>
</metadata>
</topicmeta>
</topicref>
</topicref>
<topicref id="wt.part.WTPartMaster_38896257863" format="xml" navtitle="MOTOR"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38896257884">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsGroup</category>
<data name="orgName">islan</data>
<data name="objName">MOTOR</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042591</data>
<data name="objNumber">0000042591</data>
<data name="thePersistInfo.updateStamp">2018-07-24 14:27:32.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="sis.group.contentLocation">/vegas - Scroll comp</data>
<data name="name" transidref="e8c7e887dca80fb7" xml:lang="en-US">MOTOR</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 19:40:33.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
<metadata id="WTPartUsageLink_38896272091">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">125</data>
<data name="thePersistInfo.updateStamp">2018-07-18 21:52:19.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 21:52:19.0</data>
</metadata>
</topicmeta>
<topicref id="wt.part.WTPartMaster_38895840766"
href="x-wc://6c8a5cf00e337b34:com.ptc.arbortext.charls.partlist.PartList:0000001047"
copy-to="com.ptc.arbortext.charls.partlist.PartList/0000001047.xml"
scope="local"
format="xml"
type="ipd"
navtitle="PJMTest"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38895840792">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.Content|com.ptc.sis.ContentRef|com.ptc.sis.PartsListRef</category>
<data name="orgName">islan</data>
<data name="objName">PJMTest</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042588</data>
<data name="objNumber">0000042588</data>
<data name="thePersistInfo.updateStamp">2018-08-13 19:46:14.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="name" transidref="becbbe629157aa87" xml:lang="en-US">PJMTest</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 14:05:23.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
<metadata id="WTPartUsageLink_38896257893">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">1000</data>
<data name="thePersistInfo.updateStamp">2018-07-18 19:40:18.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 19:40:18.0</data>
</metadata>
<metadata id="PartList_38895840800">
<category>com.ptc.arbortext.charls.partlist.PartList</category>
<data name="orgName">islan</data>
<data name="SIM.lastUpdated">2018-08-13 19:46:05.0</data>
<data name="type">Parts List</data>
<data name="partListType">General</data>
<data name="versionIdentifer.versionId">A</data>
<data name="Name" transidref="becbbe629157aa87" xml:lang="en-US">PJMTest</data>
<data name="number">0000001047</data>
<data name="Number">0000042588</data>
<data name="name" transidref="42dfc5d7d565be4d" xml:lang="en-US">PJMTest</data>
<data name="iteration">1</data>
<data name="thePersistInfo.createStamp">2018-07-18 13:47:57.0</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 14:05:23.0</data>
</metadata>
</topicmeta>
</topicref>
</topicref>
<topicref id="wt.part.WTPartMaster_38895950530"
href="x-wc://fba4afd93db102bb:com.ptc.arbortext.charls.partlist.PartList:0000001050"
copy-to="com.ptc.arbortext.charls.partlist.PartList/0000001050.xml"
scope="local"
format="xml"
type="ipd"
navtitle="Demo Partslist"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38895950548">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.Content|com.ptc.sis.ContentRef|com.ptc.sis.PartsListRef</category>
<data name="orgName">islan</data>
<data name="objName">Demo Partslist</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042590</data>
<data name="objNumber">0000042590</data>
<data name="thePersistInfo.updateStamp">2018-07-18 15:38:46.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="name" transidref="b5fc943b79eeb353" xml:lang="en-US">Demo Partslist</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 15:38:46.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
<metadata id="WTPartUsageLink_38896272092">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">250</data>
<data name="thePersistInfo.updateStamp">2018-07-18 21:52:19.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 21:52:19.0</data>
</metadata>
<metadata id="PartList_38895950555">
<category>com.ptc.arbortext.charls.partlist.PartList</category>
<data name="orgName">islan</data>
<data name="SIM.lastUpdated">2018-07-18 15:38:46.0</data>
<data name="type">Parts List</data>
<data name="partListType">General</data>
<data name="versionIdentifer.versionId">A</data>
<data name="Name" transidref="b5fc943b79eeb353" xml:lang="en-US">Demo Partslist</data>
<data name="number">0000001050</data>
<data name="Number">0000042590</data>
<data name="name" transidref="8aeede4e7af5d4a9" xml:lang="en-US">Demo Partslist</data>
<data name="iteration">1</data>
<data name="thePersistInfo.createStamp">2018-07-18 15:19:53.0</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 15:38:46.0</data>
</metadata>
</topicmeta>
</topicref>
<topicref id="wt.part.WTPartMaster_38895937990"
href="x-wc://20ca3aaf68ec450e:com.ptc.arbortext.charls.partlist.PartList:0000001049"
copy-to="com.ptc.arbortext.charls.partlist.PartList/0000001049.xml"
scope="local"
format="xml"
type="ipd"
navtitle="BEARING; STEEL"
locktitle="yes">
<topicmeta>
<metadata id="WTPart_38896150181">
<category>wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.Content|com.ptc.sis.ContentRef|com.ptc.sis.PartsListRef</category>
<data name="orgName">islan</data>
<data name="objName">BEARING; STEEL</data>
<data name="sis.base.ExcludeContent">false</data>
<data name="defaultUnit">nu</data>
<data name="number">0000042589</data>
<data name="objNumber">0000042589</data>
<data name="thePersistInfo.updateStamp">2018-07-18 19:33:59.0</data>
<data name="servicekit">false</data>
<data name="sis.base.ExcludeTitle">false</data>
<data name="name" transidref="a829d159e600a368" xml:lang="en-US">BEARING; STEEL</data>
<data name="serviceable">true</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 19:33:59.0</data>
<data name="lifeCycleState" key="INWORK">In Work</data>
</metadata>
<metadata id="WTPartUsageLink_38896272093">
<category>wt.part.WTPartUsageLink|com.ptc.sis.BaseUsageLink</category>
<data name="lineNumber.value">500</data>
<data name="thePersistInfo.updateStamp">2018-07-18 21:52:19.0</data>
<data name="editType">MANUAL_EDIT</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 21:52:19.0</data>
</metadata>
<metadata id="PartList_38896150188">
<category>com.ptc.arbortext.charls.partlist.PartList</category>
<data name="number">0000001049</data>
<data name="orgName">islan</data>
<data name="Number">0000042589</data>
<data name="SIM.lastUpdated">2018-07-18 19:46:16.0</data>
<data name="name" transidref="ca83f6bf72bc997e" xml:lang="en-US">BEARING; STEEL</data>
<data name="iteration">2</data>
<data name="thePersistInfo.createStamp">2018-07-18 14:41:24.0</data>
<data name="type">Parts List</data>
<data name="thePersistInfo.modifyStamp">2018-07-18 19:33:59.0</data>
<data name="versionIdentifer.versionId">A</data>
<data name="Name" transidref="a829d159e600a368" xml:lang="en-US">BEARING; STEEL</data>
</metadata>
</topicmeta>
</topicref>
</topicref>
</languageset>
</DUMP>
Thanks,
Kris.