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 78

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 78

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

615 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