I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, the query results can be inserted into the table if the definition is correct.

Ultimately the XML needs to be written to a file, but the part I am unsure about is if writing XMLQueries from scratch is the best way to generate the XML. Is there any way to have the XML schema generate an XML stub for a guide or generate template XMLQuery? The data is currently all in regular Oracle tables, but arranged quite differently, so any information on easing the export to XML based on a specific set of registered schema would be useful.

  • I think there's a way to do it if your tables match your schema (I think there's even a way to generate tables based on a schema). If your tables are differently structured, you'll probably have to write queries to transform the data into XML. – FrustratedWithFormsDesigner Feb 19 '13 at 19:35
  • I can generate a table based on the schema, but it is just a table of one XMLType column. Is that what you meant? – Leigh Riffel Feb 19 '13 at 20:17
  • @LeightRiffel: No, I'm pretty sure there was a project here a couple of years back where the DBA used some sort of Oracle tool to generate tables whose structures and relationships matched with the schema. I don't recall the name of the tool and it's possible it won't work with all schemas, but it's out there somewhere. Otherwise, it's a manual job of writing queries to massage the "normal" database data into XML structures. You still needed queries when tables matched schema, but they were much simpler. Have you tried searching/asking on StackOverflow? – FrustratedWithFormsDesigner Feb 19 '13 at 20:22
  • Searching yes, asking, no. It looks like there are tools that do the conversion, but I haven't found anything built into Oracle. – Leigh Riffel Feb 19 '13 at 21:24
  • Really? Hm. I thought the DBA said it was something in Oracle. It's possible he had a custom script that created tables from schemas using other Oracle functions. He's not around at the moment to ask, sorry. – FrustratedWithFormsDesigner Feb 19 '13 at 21:30

It is fairly direct to push an already existing select to XML: http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612

Creating XML is part of the basic SQL specifications and is available on most servers. You can check out SQL server's version of it here: http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/

  • That's where we ended up; just create the XMLQuery code from scratch. – Leigh Riffel May 28 '13 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.