reading xml file in plsql

Posted on 2015-02-01
Last Modified: 2015-02-03
Hello Experts,

I have a sample XML file which I need to read it . Where I can display the nodes and sub-nodes along with the data.
So that finally we can select the data and put into respective set of tables.

Here is my sample XML file.
Question by:Swadhin Ray
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40582958
Off the top of my head: sql loader, utl_file or dbms_lob.loadclobfromfile.

There are examples of each pretty much everywhere.

Once you get it in a CLOB in the database, look at XMLTABLE queries to parse it.
LVL 16

Assisted Solution

by:Walter Ritzel
Walter Ritzel earned 50 total points
ID: 40583055
There is this answer on the Oracle Community, from 2010:
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40583094
As I mentioned:  There are many examples if you look around.  The above post has parts of two of what I mentioned:  dbms_lob and xmltable.

If you Google around there are many more.

I would not suggest the DOM example from the post.  The DOM is good for when you need it but rarely have I needed it.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 16

Author Comment

by:Swadhin Ray
ID: 40583359
Is there any way to get the name and count of the parent and child nodes by just accessing the XML file using something like below:

SELECT xmltype(bfilename('ORA_DIR', 'XML_Sample.xml'), nls_charset_id('UTF-8')) xmlcol FROM dual;
LVL 74

Expert Comment

ID: 40583816
what is the output you're trying to get from the data you have provided?
LVL 16

Author Comment

by:Swadhin Ray
ID: 40583909
What I am looking to list like if we see the sample file there is result as parent node and under that we have ComputerSystem .

First I want to list guid's  under that we have CPUReservation numbers which is again a list .. kind of parent and child nodes.

So wanted to list the no of records and data for parent and how many child are present in that file.

parent 1 --> 3 child --> each child have 5 more sub child
LVL 74

Expert Comment

ID: 40584026
please post the data output you're trying to get from the data you have provided.

i.e. the rows and columns you are expecting
LVL 16

Author Comment

by:Swadhin Ray
ID: 40584176
What the data something like below:

ComputerSystem	residesOnVMWareDataStore	basedOn	fileSystems
204BED7DB8E030A990FB29C10D1861CD	CEEAB8B228DC3FE184AC1985754DDD5D	4624CF1E51D53F50874F6C99B729FE04	1DAC1037B63D3CAABFCB3F0AEE3827F5
204BED7DB8E030A990FB29C10D1861CD	CEEAB8B228DC3FE184AC1985754DDD5D	4624CF1E51D53F50874F6C99B729FE05	E92B6171FB83309A9AB0D65987BCE7A8
204BED7DB8E030A990FB29C10D1861CD	CEEAB8B228DC3FE184AC1985754DDD5D	4624CF1E51D53F50874F6C99B729FE05	7B5E9FB7C0993D55AC77BC5C3F47536D

Open in new window

LVL 74

Accepted Solution

sdstuber earned 350 total points
ID: 40584359
SELECT x.computersystem,
           xmlnamespaces(DEFAULT 'urn:www-collation-com:1.0'), '//ComputerSystem'
           PASSING  xmltype(bfilename('ORA_DIR', 'XML_Sample.xml'), nls_charset_id('UTF-8'))
           COLUMNS computersystem PATH './@guid', self XMLTYPE PATH '.'
       ) x,
           xmlnamespaces(DEFAULT 'urn:www-collation-com:1.0'), '//residesOnVMWareDataStore'
           PASSING x.self
           COLUMNS residesonvmwaredatastore PATH './@guid',
                   basedon PATH './basedOn/@guid',
                   self XMLTYPE PATH '.'
       ) y,
           xmlnamespaces(DEFAULT 'urn:www-collation-com:1.0'), '//fileSystems'
           PASSING y.self
           COLUMNS filesystems PATH './@guid'
       ) z
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 40585504
@sdstuber : thanks a ton for this , I got the what mistake I was doing by referring your code.  Without providing xml namespaces I was trying to access it by passing the xml type and putting everything on the clob column.

@ slightwv : Thanks for your suggestion

@Walter: Thanks for sharing the link.

Thanks a lot to all the experts, giving their valuable time to solve others problem.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question