10 Replies Latest reply on Sep 30, 2018 5:09 AM by 908511

    XML to Oracle Table load

    908511

      Experts,

       

      Have created XML table as below.

       

      not resoved so closing the thread

      Thanks,

      Vasu

        • 1. Re: XML to Oracle Table load
          cormaco

          Please help me to load all roots,Categories, Data and etc..

          And what else?

          Do you think we work for you for free?

          • 2. Re: XML to Oracle Table load
            908511

            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.

             

            select x.*

            from XML_TAB_LD     tbl

               , xmltable(

                      '/DUMPmap/languageset/topicref/topicmeta/metadata'

                      passing tbl.xml_dc

                      columns

                       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')

                 ) x;

            • 3. Re: XML to Oracle Table load
              Jason_(A_Non)

              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; 
              
              • 4. Re: XML to Oracle Table load
                908511

                Thanks Jon. I am caring about the column " STR_NAME"

                 

                which maps to

                 

                Mapping fields

                 

                 

                  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

                 

                 

                 

                $IsRootwt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsRoot
                $IsGroupwt.part.WTPart|com.ptc.sis.Base|com.ptc.sis.BaseDiv|com.ptc.sis.IsGroup
                • 5. Re: XML to Oracle Table load
                  Jason_(A_Non)

                  So the XPath's you really care about are (at least they work in XMLSpy, not tested in Oracle)

                  /DUMP/languageset/topicref/topicmeta/metadata[category[contains(text(), 'IsRoot')]]/data[@name='objName']

                  /DUMP/languageset/topicref/topicref/topicmeta/metadata[category[contains(text(), 'IsGroup')]]/data[@name='objName']

                   

                  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

                  • 6. Re: XML to Oracle Table load
                    908511

                    can you please advise, how can we load the data for Category and DATA Name which is under metadata?

                    • 7. Re: XML to Oracle Table load
                      Jason_(A_Non)

                      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.

                      • 8. Re: XML to Oracle Table load
                        908511

                        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)
                        
                        • 9. Re: XML to Oracle Table load
                          Jason_(A_Non)

                          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?

                          • 10. Re: XML to Oracle Table load
                            908511

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