Please help me to load all roots,Categories, Data and etc..
And what else?
Do you think we work for you for free?
I have written a query as below to retrieve Parent,Chilld,grandchils.... but its not giving required o/p.. please have a look and correct the query.
from XML_TAB_LD tbl
STR_ID varchar2(35) path '@id'
,STR_NAME varchar2(250) path '@data'
,CREATED_DT timestamp default to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS')
,LAST_MODIFIED timestamp default to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS')
I'll bite on a few things wrong. First, the root node is called DUMP, not DUMPmap as your XPath suggests. Second, data is a child node of metadata, not an attribute of it so it should just be 'data'. Third, there are lots of data elements so which one. Fourth, no clue what you are trying to do with the CREATED_DT and LAST_MODIFIED as you are not selecting anything from the XML so why are they there?
Here's a thought for where you may be trying to go to show you how some various things work
WITH xml_tab_ld AS (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?> <DUMP xmlns:xs="http://www.w3.org/2001/XMLSchema" xml:lang="en-US"> <data name="thePersistInfo.createStamp">2018-07-18 13:58:18.0</data> <data name="thePersistInfo.modifyStamp">2018-07-18T13:58:19.0</data> <!-- Modified to make proper date format --> </DUMP>') xml_dc FROM dual) -- The above is created to simulate your data table because I did not create it in my system. -- You only care about what is below this line select x.created_dt, coalesce(to_timestamp(x.created_dt2, 'YYYY-MM-DD HH24:MI:SS.FF'), SYSTIMESTAMP) created_dt2, x.modify_dt, x.modify_dt2, coalesce(x.modify_dt3, SYSTIMESTAMP) modify_dt3 from XML_TAB_LD tbl , xmltable( '/DUMP' passing tbl.xml_dc COLUMNS CREATED_DT VARCHAR2(25) PATH 'data[@name="thePersistInfo.createStamp"]', CREATED_DT2 VARCHAR2(25) PATH 'data[@name="thePersistInfo.createStamp2"]', MODIFY_DT TIMESTAMP PATH 'data[@name="thePersistInfo.modifyStamp"]', MODIFY_DT2 TIMESTAMP PATH 'data[@name="thePersistInfo.modifyStamp1"]' DEFAULT to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS'), MODIFY_DT3 TIMESTAMP PATH 'data[@name="thePersistInfo.modifyStamp1"]' ) x;
Thanks Jon. I am caring about the column " STR_NAME"
which maps to
STR_NAME mapped to : /DUMP/languageset/topicref/topicmeta/metadata[./category='$IsRoot']/data[@name='objName'] STR_NAME mapped to : /DUMP/languageset/topicref//topicref/topicmeta/metadata[./category='$IsGroup']/data[@name='objName'] parent::topicref $IsRoot wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsRoot $IsGroup wt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsGroup
So the XPath's you really care about are (at least they work in XMLSpy, not tested in Oracle)
I'll leave it up to you to start putting into an XMLTable as no one still knows how this data is supposed to map into the table you are trying to load
can you please advise, how can we load the data for Category and DATA Name which is under metadata?
Show what you expect the rows and columns from a query against that XML would return. You've been asked this in other threads. Also show that you've made some effort at creating this yourself by updating the SQL statement you've started.
Please find below Target table and the mapping columns from XML to Oracle. Please share the query to load data fro XML to Target Oracle table. Below is the mapping for reference.
STR_ID NUMBER --> Sequence Number STR_NAME VARCHAR2(250 BYTE) --> Category ( from XML) PRD_HIER_ID NUMBER --> Sequence Number PARENT_STR_ID NUMBER --> Parent ID ( am not sure how we know who is parent from XML) DISPLAY_ORDER VARCHAR2(4 BYTE) --__> Parent and their Children and their grannd Children ( tr)ee Hierarchy CREATED_TS TIMESTAMP(6) LAST_MODIFIED_TS TIMESTAMP(6) S_STR_ID VARCHAR2(250 BYTE) -- Metadata_ID STR_TYPE VARCHAR2(40 BYTE) -- Type from XML file STR_DATA VARCHAR2(250 BYTE) --> ( Data Names (/data) from XML)
That doesn't answer the previous question I asked of "Show what you expect the rows and columns from a query against that XML would return". That doesn't mean you have working SQL, but show how data from the XML should come out in row/column format for loading into those 8 columns you have put comments by. Our job is not to map the data from the XML to the table but to provide assistance on building the XML that extracts the information in the order/format you need. If you can't provide the expected output, how are we supposed to do it?
Here I mean load the complete XML file to Oracle Table. Date columns ( create date and Last_modified are manually I have created to track when the files got created and when it got updated).