Toad and XML

Toad for Oracle & DevOps Toolkit
1

I have an XML file of data in the expected format:

John

20080911

20080915

NULL

Jane

20080911

20080915

NULL

And so on through the data records.

What I would like to do is take the data itself and put it into csv format. For
example:

First name, hire date, start date, end date

John, 20080911, 20080915, NULL

Jane, 20080911, 20080915, NULL

Toad can open XML files. Question: Is there some way the user can redefine the
information in the file so Toad can then save it in csv format?

In the alternative, is there perhaps someone familiar enough with Excel to do
the same there without having to author code? I’m looking for a
quick-n-dirty way of achieving this one-time task without having to manually
format every line.

My last resort is to load it up into a temp table in oracle and write some
quick-n-dirty code to parse and format it.

Roger A. Simoneau
Alberta Blue Cross
(780)498-8837

3

Roger, if this in one-time, it’s probably not be worth getting fancy. But here
are a couple of fancier approaches.

But what about loading it into Oracle as a XMLType and shredding it into the
appropriate database tables, then doing a standard grid export to convert it to
the CSV that you need? I’m assuming you have a schema for the XML, there are
supposed to be ways to tell the database what to do with the various schema
elements and have that happen automatically.

Or, equivalently, just put the XML document into the database and use the XQuery
features to grab the elements you want as query results.

I’m talking out of my hat here – all my database XML work so far has been going
from the relational tables to XML documents, not the reverse. Doing the
shredding process is on my schedule for “real soon now”.

I guess my main point is that Oracle handles XML pretty well so you can
certainly do this on the database side if you want – without writing your own
parser.

– jim

James F. Hudson
Wisconsin Department of Natural Resources
Madison, WI
(608) 267-0840

4

I’ve gone from XML to Relational Views of the XML… you have to really
understand XML to do it tho…

You’ll begin to understand when you deal with the: Extract, ExtractValue,
XMLSequence and TABLE functions…

I did it. It works, but is really putzy.

5

You can use XML transformations (XSLT) to do this as well. From what I
understand there’s a learning curve there and we no longer have anyone on
the team that’s geeky enough to assist. There are several freeware and
shareware apps that say they can do this as well.

Michael

6

I resemble that remark about “geeky enough” to assist with XML :slight_smile:

Is this is a one time thing I would just echo what has already been written.
Otherwise, the easiest method is likely into XMLType column and execute some
XQuery against it. Alternatively, you could try importing it into some other DB
tool that will do a lot of the generation for you. They exist too, but they
usually make a big mess. This looks like it might be just simple enough to work
though.

7

Good Morning;

After spending a couple hours going through all the tools on my desktop that can
“read and display” XML files, I decided to write the parser myself.

File transfer and load into Oracle: 2 minutes

Author code to handle the parsing and put out the data in the format I want: 10
minutes

Debug the subtle endless loop (oops): 2 minutes

Total time to process the file: 15 minutes

Finding out that there doesn’t seem to be any tool written to handle what
seems to me to be something obvious that is likely to have occurred many times
yet the time to author the parser was trivial: priceless!

What I find to be obvious about the situation: A business user gets an XML file
that is simply a data dump and would like to display it in a format that they
are familiar with in a spreadsheet.

Ah well… I guess perhaps everyone else is waiting on someone else to put
that “phenomenal feature” into their application first. Either that
or some FLOSS spreadsheets can do what I wanted but I’m limited because
none of them are on my work-sanctioned desktop. Now I’m curious and will
have to check out gnumeric when I get home.

Roger S.

8

“ to author the parser was trivial ”

I should note for the humor impaired that I recognize writing a parser for a
very specific file is trivial in comparison to doing up the code to be able to
handle the full XML format!

As I mentioned earlier, I was looking for a quick-n-dirty method so the code is
just that. It’s not anywhere near as flexible as it would have to be in
order to handle an unknown file with only knowledge of XML to guide the
developer.

So… my apologies if any developers who author XML parsers were offended!

RAS

9

I use the NetBeans IDE for dealing with XML files. It has some great
browsing and reformatting capabilities. Additionally, it’s free.

Mike

10

I just did this same kind of thing using SQL*Load. I had to put in my XML
delimiters for each row, etc. but it worked slick as frog snot!

cust_transaction_dtl.ctl
LOAD DATA

INTO TABLE "TRANS_HIST"."CUST_DTL" APPEND
(dummy filler enclosed by '',
SITE_CODE ENCLOSED BY '' AND '',
INVOICE_NUMBER ENCLOSED BY '' AND '',
CLOSE_DTS DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '' AND
'',
CUSTOMER_NUMBER ENCLOSED BY '' AND '',
STATUS_TYPE ENCLOSED BY '' AND '',
NEW_CUSTOMER_FLAG ENCLOSED BY '' AND '',
CUSTOMER_NAME ENCLOSED BY '' AND '',
CUSTOMER_ADDRESS ENCLOSED BY '' AND '',
CUSTOMER_CITY ENCLOSED BY '' AND '',
CUSTOMER_ST_PROV_CODE ENCLOSED BY '' AND
'',
CUSTOMER_ZIP_CODE ENCLOSED BY '' AND '',
CUSTOMER_AREA_CODE ENCLOSED BY '' AND
'',
CUSTOMER_PHONE ENCLOSED BY '' AND '',
AFFILIATED_CO ENCLOSED BY '' AND '',
dummy2 filler enclosed by '')

Hope the helps.

David
UtahToad@gmail.com

Simoneau, Roger wrote:

“ to author the parser was trivial ”

I should note for the humor impaired that I recognize writing a parser for a
very specific file is trivial in comparison to doing up the code to be able
to handle the full XML format!

As I mentioned earlier, I was looking for a quick-n-dirty method so the code
is just that. It’s not anywhere near as flexible as it would have to
be in order to handle an unknown file with only knowledge of XML to guide
the developer.

So… my apologies if any developers who author XML parsers were
offended!

RAS

 PLEASE NOTE:

This communication, including any attached documentation,
is intended only for the person or entity to which it is addressed,
and may contain confidential, personal and/or privileged information.
Any unauthorized disclosure, copying, or taking action on the contents
is strictly prohibited. If you have received this message in error,
please contact us immediately so we may correct our records.
Please then delete or destroy the original transmission and any subsequent reply.
Thank you.

11

Morning David,

I just did this same kind of thing using SQL*Load. I had to
put in my XML delimiters for each row, etc. but it worked
slick as frog snot!
...

Now that's what I call "thinking outside the box" - or laterally as we
old folks used to call it.

Excellent solution.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

12

Roger,

are you aware of an excel capability to directly open and edit XML files?

emha.

13

SITE_CODE ENCLOSED BY '' AND ''

Ahh… it seems I had to work with a less-friendly xml format.
Unfortunately, all mine where enclosed with and

For example:

.

. John

.

.

. Doe

.

.

. Jane

.

.

. Doe

.

So… while it was helpful the entries were designated, it wasn’t so
helpful the attributes weren’t identified by their explicit attribute
rather than as an attribute. This, however, might have to do with the fact I
pulled the report from OID using – probably MS’ – LDAP query
tool.

It would certainly have been much easier if I’d had:

johndoe

John

Doe

and so on…

Heh, Excel would have probably displayed it correctly in that case too!
I’ll have to test that.

Roger S.

14

Hey emah;

are you aware of an excel capability to directly open and edit XML files?

Yes I am. Unfortunately, it’s the layout of the file that causes it to not
be readily worked with. David outlined how he handled his XML file. I modified
my XML file to match his format and Excel could then open it and display the
data in the format I was looking for.

So… my “itch” at this point: figure out how to tell the LDAP
query tool to produce the data file in the format that is user friendly :wink:

Roger S.

15

Hi,

Your thread was quite helpful… i got enough idea for loading of my own file…could you pls help me out with fol. file :

true
00144105-009-000
00144105-009-000CV Magellan Arteriocyte Inc 1164385
SoldTo
0001164385

false
Brian Smith
false

11/15/2007
12/31/2013
01/10/2013
Quarterly
10/12/2012
10/12/2012
false

0

Others
true
false
false
false
false
draft
false
true
CV Magellan Arteriocyte Inc 1164385

true
MAG100
Magellan

need to load such file in DB oracle 9.7.2

Would be a grt help !!!

~ekta