2 Replies Latest reply on Aug 30, 2013 5:29 PM by garywicke

    Can I create an XSD from XML data in a CLOB?



      Oracle EE on Solaris.

      Very much an XML newbie so please be kind! 


      I have spent the past few days pouring through the documentation and various blogs, especially this one, but ... I need some help.


      I am trying to extract XML data stored in a CLOB and produce a flat file for the user's consumption.


      They have sent me what they think is the XSD for the XML data but when I look at the data in the CLOB I don't see the tags documented in the XSD.


      I'd like to produce an XSD based on the actual data to compare with what they've sent.


      Is this possible?


      I am able to query the XML data using the tags I see in the data using XMLTable and it's working fine.


      Any help is greatly appreciated.



        • 1. Re: Can I create an XSD from XML data in a CLOB?

          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?


          Some questions

          - 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.

          • 2. Re: Can I create an XSD from XML data in a CLOB?



            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.


            Thanks again!