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
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 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 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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

764 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