Avatar of gs79
gs79
 asked on

generic dbms_sql utility to load any file

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
Oracle Database

Avatar of undefined
Last Comment
gs79

8/22/2022 - Mon
SOLUTION
Swadhin Ray

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Swadhin Ray

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
slightwv (䄆 Netminder)

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

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

>>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?
gs79

ASKER
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
slightwv (䄆 Netminder)

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gs79

ASKER
Yes XML node will be the column name..

the xmls will be something similar to here:

https://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
slightwv (䄆 Netminder)

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?
gs79

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gs79

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