generic dbms_sql utility to load any file

gs79
gs79 used Ask the Experts™
on
Instead of writing a xquery to extract xml data into table specific to a file, can dbms_sql be used to extract ANY xml data into table. Something similar to Tom's example/application for DBMS_SQL to load any flat file in one of his blogs? Please let me know if you have come across any such utility..

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have a look to DBMS_XMLSAVE package which help to slove your issue.
First read the file as CLOB and use DBMS_XMLSAVE to parse it into a table.

For more details you can check the link on DBMS_XMLSAVE:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlsav.htm

:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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?

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
Yes XML node will be the column name..

the xmls will be something similar to here:

http://www.experts-exchange.com/Database/Oracle/Q_27686997.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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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?

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>the column name should be built dynamically something like:

That sort of makes my point.  Since the individual nodes themselves are not the columns and it is some pattern that you made up, it will be impossible to write generic code to magically create the mapping.

How far back do you go to create the colum name.  The FULL path was:
/response/headlines/headlinesItem/links/mobile/href

How will the code know the column isn't:  headlinesItem_links_mobile_href or headlines_headlinesItem_links_mobile_href or just mobile_href?

I'm afraid what you are asking for really isn't feasable.  It is probably possible for simple XML but not really for complex XML.  Too many variables to have to code for.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial