[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

656 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