Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
1

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.

share|improve this question
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 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 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 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 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 at 21:30
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.