Welcome to the XML side of the world, where you will love it and curse it, just like any other piece of technology.
So as I understand it, the scope of your operation is to extract information from an XML file and then write that information to disk, correct?
- Why is the XML stored in a CLOB instead of an XMLType column? Without knowing the system history, it is a valid question.
- How big are the XML? The reason I ask this is because when the XML is stored in XMLType columns (of Object Relational structure or SECUREFILE BINARY XML format), then Oracle can parse the XML substantially easier than when it is stored as a CLOB. If the XML is fairly small, you may see no performance difference between the two.
The Oracle DB itself has no built in ability to generate a schema from an XML file. In order to do that, you would need to use some third party tool that has that functionality built in. For example, XML Spy can do this. Other tools can as well, but that is what came to mind first as it is what I use. The one thing to remember is that the schema it builds represents that one instance of XML. Other instances of XML may be different and not validate against the schema you generated, but still have been valid against the unknown original schema.
You can register the schema in the database and then use the XML to create an XMLType instance associated to the schema and then validate the XML that way. One example can be found at XML DB FAQ and here is another example Re: XML Schema validation
Hope that helps you continue on.
Thanks very much for the feedback. Every thing I read helps a little to understand the as-of-yet mysterious world of XML.
As the saying goes, s__t rolls downhill and I'm at the bottom of the hill at the moment. I am supporting the database that supports the application that is supported by people many miles away who don't communicate much if anything about what the application is doing and what it needs. So, I didn't design the way the application works nor did I have any input into the process. So much for my disclaimer. :-)
This particular extract exercise is a one time, or possibly very few times, event so I'm not too concerned about performance or even ongoing efficiency.
I need to extract two (2) sets of data from the XML represented by 2 tag paths. I kind of stumbled onto the first one so it's in good shape and the extract to the flat file is pretty much finished.
The 2nd set is causing me trouble because I can't find the tags that supposedly represent the data I need to extract. Hence I was trying to generate an XSD document from the data, even if it's not 100% complete according to the original XSD. That way I can at least see the path I need to take to get to the data I need to extract.
I will examine the examples you referenced and I sincerely appreciate your taking the time to point me in the right direction and ask some interesting questions that I will send back to the application people. I'm not hoping for much but it feels good to ask.