ThatJeffSmith

SQL Developer does XML

If you’re looking for insight or guidance on XML, XML in the database, or XMLDB – then please keep looking. I have not much to add here. What I am here to talk about is some of the support that SQL Developer offers for XML in the database. You may have heard me say ‘I do not like XML.’ What I mean by that is ‘I do not like poor design decisions, and XML seems to be frequently misued.’ Just to be clear, I don’t mean to malign the technology here!

Don’t have any XML to play with?

Creating tables with XML columns

You can use SQL Developer’s Create Table dialog. If you do a lot of ad hoc table creates like me, then you will appreciate the simplified wizard. Ninety-five percent of the time, you just want to store numbers, dates, and text. But when you do want to store things like timestamps and XML, you’ll need to use the ‘Advanced’ mode.

Creating tables with SQL Developer

To access the SYS.XMLTYPE ‘type’, you will need to access the ‘Complex’ datatype list and navigate to the ‘SYS’ schema. You can then set any of the XML specific options. You can read the nitty-gritty details on XMLTYPE here.

Need some XML data to play with?

XML data is pretty easy to put together, and that’s probably one of the primary benefits to using XML – it’s so flexible. But, if you’re lazying like me, you just want to click-click and have your XML data. So here’s something you could do – query your PL/SQL source in SQL Developer and save the data out to XML.

Exporting your query results to XML

One way is to create a new record and just load the XML data to the XML column. Wait Jeff, couldn’t I just store my XML in a CLOB? Yes, yes you could. Here’s what the Oracle DOCS have to say about XMLTYPE vs CLOB.

Now, how do we load this data to our table?

They say a picture is worth a million words, but I’ll settle for 50 here.

Loading XML from a file to a table (single row)

Instead of clicking on ‘Load’ you can also choose ‘Editor.’ ‘Load’ is OK if you do not want to edit the data much before you INSERT it. If you want to bring up an XML editor and input the data manually or load an XML file and do some work on it before uploading it, then use the ‘Editor’ option.

This will create a temporary XML file and invoke the XML editor.

SQL Developer's XML Editor

Once you hit the ‘Save’ button, this will automatically load the data to Oracle. You will then to COMMIT your changes using the button or F11.

Preview and COMMIT changes to your table record.

Once the data is in the table, you can preview it by mousing over the XML cell in the data grid. If you double-click and hit the ‘Edit’ button again, you get a text preview of the XML, or you can invoke the full XML editor again.

SQL Developer also supports working with XML Schemas and the XML DB Repository. For more info, I really suggest reading the Oracle® XML DB Developer’s Guide first.