Page 1 of 1

Load XML file oracle Table

Posted: Fri Sep 28, 2018 5:13 am
by kristen
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

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>
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.

Re: Load XML file oracle Table

Posted: Fri Sep 28, 2018 7:40 am
by Radu
Hi Kris,

This is a forum for Oxygen XML Editor. Oxygen has support to browse an Oracle database and show the information from a table, to export content to XML from a table and to run SQL queries on a table:

https://www.oxygenxml.com/doc/versions/ ... level.html

Oxygen does not have support to map XML content to a table structure, maybe you could ask around on the Oracle support channels.

Regards,
Radu

Re: Load XML file oracle Table

Posted: Fri Sep 28, 2018 6:22 pm
by kristen
Radu,

I will take care of mapping. Can you please let me know the script to get parent,child...

Thanks

Re: Load XML file oracle Table

Posted: Mon Oct 01, 2018 7:33 am
by Radu
Hi Kristen,

Maybe you can create an XSLT stylesheet in Oxygen and apply it over the XML document to obtain SQL content, content which you can later execute to populate the database table. Maybe you can google for "generate sql using xslt".

Regards,
Radu