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
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.
-
Radu
- Posts: 9434
- Joined: Fri Jul 09, 2004 5:18 pm
Re: Load XML file oracle Table
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
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
<oXygen/> XML Editor
http://www.oxygenxml.com
-
Radu
- Posts: 9434
- Joined: Fri Jul 09, 2004 5:18 pm
Re: Load XML file oracle Table
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
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
<oXygen/> XML Editor
http://www.oxygenxml.com
Return to “General XML Questions”
Jump to
- Oxygen XML Editor/Author/Developer
- ↳ Feature Request
- ↳ Common Problems
- ↳ DITA (Editing and Publishing DITA Content)
- ↳ SDK-API, Frameworks - Document Types
- ↳ DocBook
- ↳ TEI
- ↳ XHTML
- ↳ Other Issues
- Oxygen XML Web Author
- ↳ Feature Request
- ↳ Common Problems
- Oxygen Content Fusion
- ↳ Feature Request
- ↳ Common Problems
- Oxygen JSON Editor
- ↳ Feature Request
- ↳ Common Problems
- Oxygen PDF Chemistry
- ↳ Feature Request
- ↳ Common Problems
- Oxygen Feedback
- ↳ Feature Request
- ↳ Common Problems
- Oxygen XML WebHelp
- ↳ Feature Request
- ↳ Common Problems
- XML
- ↳ General XML Questions
- ↳ XSLT and FOP
- ↳ XML Schemas
- ↳ XQuery
- NVDL
- ↳ General NVDL Issues
- ↳ oNVDL Related Issues
- XML Services Market
- ↳ Offer a Service