reading xml file in plsql

Posted on 2015-02-01
Medium Priority
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
  • 4
  • 3
  • 2
  • +1
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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 200 total points
ID: 40583055
There is this answer on the Oracle Community, from 2010:
LVL 79

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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 17

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 17

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 17

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 1400 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 17

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

589 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