# Toad and XML **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [November 19, 2009, 4:13pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977 "2009-11-19T16:13:19Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 19, 2009, 4:13pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977/1 "2009-11-19T16:13:19Z")
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 ---
**Author:** ![wimdelange\_062](https://avatars.discourse-cdn.com/v4/letter/w/9fc348/32.png) [@wimdelange\_062](https://forums.toadworld.com/u/wimdelange_062)\ **Post date:** [November 20, 2009, 1:03am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/2 "2009-11-20T01:03:32Z")
---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 8:44am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/3 "2009-11-20T08:44:59Z")
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 ---
**Author:** ![steve.boothweenergie](https://avatars.discourse-cdn.com/v4/letter/s/c37758/32.png) [@steve.boothweenergie](https://forums.toadworld.com/u/steve.boothweenergie)\ **Post date:** [November 20, 2009, 9:02am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/4 "2009-11-20T09:02:34Z")
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. ---
**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\ **Post date:** [November 20, 2009, 9:08am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/5 "2009-11-20T09:08:21Z")
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 ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 10:23am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/6 "2009-11-20T10:23:30Z")
I resemble that remark about “geeky enough” to assist with XML 🙂 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. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 10:54am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/7 "2009-11-20T10:54:05Z")
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. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 11:40am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/8 "2009-11-20T11:40:54Z")
> > “ 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 ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 12:46pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977/9 "2009-11-20T12:46:32Z")
I use the NetBeans IDE for dealing with XML files. It has some great browsing and reformatting capabilities. Additionally, it’s free. Mike ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 20, 2009, 4:24pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977/10 "2009-11-20T16:24:35Z")
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](mailto: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. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [November 23, 2009, 3:15am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/11 "2009-11-23T03:15:36Z")
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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 23, 2009, 6:44am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/12 "2009-11-23T06:44:39Z")
Roger, are you aware of an excel capability to directly open and edit XML files? emha. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 23, 2009, 12:30pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977/13 "2009-11-23T12:30:38Z")
> > 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. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [November 23, 2009, 12:57pm UTC](https://forums.toadworld.com/t/toad-and-xml/8977/14 "2009-11-23T12:57:33Z")
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 😉 Roger S. ---
**Author:** ![ektubbe](https://avatars.discourse-cdn.com/v4/letter/e/58956e/32.png) [@ektubbe](https://forums.toadworld.com/u/ektubbe)\ **Post date:** [November 10, 2012, 4:58am UTC](https://forums.toadworld.com/t/toad-and-xml/8977/15 "2012-11-10T04:58:23Z")
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