8

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.

Improve this question
6
  • 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 at 21:30

1 Answer 1

Reset to default
1

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/

Improve this answer
1
  • That's where we ended up; just create the XMLQuery code from scratch.
    – Leigh Riffel
    Commented May 28, 2013 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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