SQL Developer does XML

thatjeffsmith SQL Developer 17 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 17

  1. I am having some problems updating an XMLtype column when in the data view using SQL Developer 4.1.1.19

    — SQL Developer 4.1.1.19
    Build after save finished
    INSERT INTO “schema”.”table” (CLIENT, IDOC, PROCESSED) VALUES (‘test’, NULL, ‘C’)
    One error saving changes to table “schema”.”table”:

    I made many test and i think that the problem became from white-space (SQL Developer) in Path

    sqldev.xml:/C:/Users/user_data/AppData/Roaming/SQL Developer/tmp/XMLType3765140783876499103.xml

    Do you know if is possible to change path for xmltype file template?

    regards

  2. I have to read an table that has an CLOB column with XML data, currently the table show a blank column for the XML data, i verified thru another tool (DB2 SQL Studio) that it has data, how do I read that data in SQLDeveloper? I have only read access to the table. I have SQLDeveloper 3.0.04 installed.

    1. thatjeffsmith Post
      Author
  3. This is no longer working in SQL Developer v4.0.1, I see XML columns showing as ‘XML’ word in it, not able to see the whole payload, any idea?

    1. thatjeffsmith Post
      Author

      XML has a lot of variables that can cause problems if they don’t align perfectly.

      What version of Oracle DB?

      Are you using an Oracle Client (OCI thick connections) in SQL Developer?

      How exactly is the XML stored/defined in your table?

  4. Sorry, my English is very bad.
    I have problem with XML Editor on Oracle SQL Dev(3.2.20.09).
    Recipe with check ‘Use OCI/Thick driver’ don’t have any effect.
    But! I have another vers. of Oracle SQL Dev(3.1.07.42) and there XML Editor works.
    oracle client 11.2.0.3.0.
    Oracle SQL Dev(3.2.20.09) use ojdbc6.jar, but Oracle SQL Dev(3.1.07.42) use ojdbc5.jar. When I switch SQL Dev(3.2.20.09) to use ojdbc5.jar XML Editor began to earn.
    Can you confirm this?
    Could this cause any problems?

  5. thatjeffsmith Post
    Author

    When you use Connect Identifier, you’re forcing SQL Dev to use the OCI/Thick driver to connect versus just using the straight up jdbc driver.

    I’m guessing if you enabled ‘Use OCI/Thick driver’ in the preferences and switched back to ‘Alias’ for your connection, it would also work.

    Can you tell me what version of Oracle database you are connecting to?

    What version of Oracle Client you have installed on your machine?

    Anyways, at least you got it working, but now let’s figure out what the issue is so you can avoid it going forward!

  6. Hi,
    I’ve recently moved from Oracle SQL Developer 1.5.5 (32bit) to 3.2.10.09 (64bit), and it looks like I can’t view or edit XMLType data any more. I just see (XMLType) in the grid’s cells and clicking does nothing.
    Been through all the prefs and scanned t’inters but this is the only page that even comes close. Do you have any ideas what I need to do to get this ability back?
    Thanks!
    M

    1. thatjeffsmith Post
      Author
      1. No, it just has (XMLTYPE) in the cell.
        I’ve just found out how to fix it (but not why). I needed to set up the connection via a TNS connect identifier rather than an alias. Now I have my XML data back!
        Words fail me at this point…

  7. @Jeffs

    Just saw a “ping” on my site originated from this site ;-), so I had a look.

    @Michael
    A good alternative, especially if you are as poor as me, Oracle’s JDeveloper is also a good alternative to the two options you mention (although still hoping for an embedded XML editor, like JDeveloper in SQL Developer). It comes packed with a lot we probably don’t need in the Database Realm of Things but it has very good support for XML(DB) functionality in the Oracle database (and outside it) and its “free”.

    Regarding the “Jeff, couldn’t I just store my XML in a CLOB?” bit, that is actually very easy.

    If you like performance use the XMLType datatype in Oracle (it was designed for handling XML) and if you like a steep performance learning curve use CLOB as your storage item. 😉 BTW the old referenced Oracle doc mentions a lot of functions and operators (extract, extractvalue etc) that are deprecated by Oracle. Its probably best to have a look at the 11gR2 XMLDB Developers Guide doc. if not only because XMLDB is a mandatory (core) option in the latest Oracle database versions.

    1. thatjeffsmith Post
      Author
    1. JeffS Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *