Load XML file oracle Table

Questions about XML that are not covered by the other forums should go here.
kristen
Posts: 2
Joined: Fri Sep 28, 2018 4:56 am

Load XML file oracle Table

Post by kristen » 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

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.

Radu
Posts: 6450
Joined: Fri Jul 09, 2004 5:18 pm

Re: Load XML file oracle Table

Post by Radu » Fri Sep 28, 2018 7:40 am

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
Radu Coravu
<oXygen/> XML Editor
http://www.oxygenxml.com

kristen
Posts: 2
Joined: Fri Sep 28, 2018 4:56 am

Re: Load XML file oracle Table

Post by kristen » Fri Sep 28, 2018 6:22 pm

Radu,

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

Thanks

Radu
Posts: 6450
Joined: Fri Jul 09, 2004 5:18 pm

Re: Load XML file oracle Table

Post by Radu » Mon Oct 01, 2018 7:33 am

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
Radu Coravu
<oXygen/> XML Editor
http://www.oxygenxml.com

Post Reply