generic dbms_sql utility to load any file

And another way can be by using XSU utility , to know more about this , have a look to the below link:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm

I doubt what you are wanting to do exists.  XML can be much more complex than a CSV file.

Your program would have to be smart enough to figure out the XML in such a way to know what values goes where.

For example, how would you 'know' what goes into what columns given the following three examples?  All have the same data but different formats.

Example 1:
<a>
  <b>
     <c>Hello</c>
    <d>World</d>
  </b>
</a>

Example 2:
<a>
   <b c="Hello">
       <d>World</d>
   </b>
</a>

Example 3:

<a>
   <b c="Hello" d="World" />
</a>


Now if you knew the format of the XML maybe through a Schema or XSLT passed along with it, you might be able to parse the data properly.  But it would take some coding on your part to pull it off.

Thank you for your responses..I am exploring DBMS_XMLSAVE package..

@slightwv
There is no XSLT passed along with xml file..the schema will also be not known i guess..XML files could come from different vendors..

In that case a generic utility to load any xml is not possible at all? not even with dbms_sql?

please let me know..

Thanks

>>In that case a generic utility to load any xml is not possible at all?

Given my three examples above, how do you know what value would go into what column?

Now imagine much more complex XML.

I guess I should ask:  Do you need to store the data relationally?

normally the file we get is in format in example 1. column c &d will have value hello and world.

the table I am trying to load will be still be relational but highly de-normalized.

in the example above, the values inside the tags that are at the same level as tag b, will be each record in the table. If there are values one level above the level of tag b then they will be the columns constituting the repitative values..


hope I am clear..

thanks

>>hope I am clear..

Not really...

So you are looking to parse XML and insert the value of any text node into a column with the node name?

>>then they will be the columns constituting the repitative values..

I don't understand this.

If your XML node names match up with your column names then you might have a chance.

Yes XML node will be the column name..

the xmls will be something similar to here:

https://www.experts-exchange.com/questions/27686997/xml-data.html

in the above link we are extracting the values for few nodes..

Basically I want to extract all the nodes that has values with node name becoming the column name..

If this is doable, then do I have to explore xml functions/procs in dbms package or  can we do by just using dbms_sql..

please advice..

Thanks

I remember that question.

Unfortunately I don't believe you will be able to create anything generic enough to handle XML that complex for ALL possible combinations.

>>node name becoming the column name

links/mobile/href  and links/web/href.  What is the column to insert these two values into?  You can only have one 'href' column per table so the node name cannot be the column name.  It would have to be some combination of node/parent_node/grand_parent_node name combination.

If you had some method to know what XML you are being passed then you can create seperate methods to deal with them.

Since you already mention the main table is heavily denormalized, what is this main table used for?

at that time we defined the col names explicitly:

web_link varchar2(200) PATH 'web_link',
mobile_link varchar2(200) PATH 'mobile_link'

for making this generic..

the column name should be built dynamically something like:

links_mobile_href
links_web_href

We will be extracting xml data into kind of staging table. This table will be dynamic depending on what is in the xml. I assumed that with the way the data is in XML we will extract the content into a de-normalized table..

Thanks

Thanks for the responses..Now I understand that its not feasible to have a generic program to read the xml..