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.
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.
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.
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.
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.
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.
21 Comments
HI, I’ve inserted xml into tables with the table viewer on a number of occasions, however recently if I have a table with just one column of xmltype the Insert New Row button is grayed out. If I have a table with other column types including xml I can click the Insert New Row button but when I click on the xml column the Load option is grayed out. I’m using Version 17.3.1.279. Thanks.
In addition to my email above, this only seems to occur in one database withthe Insert New Row working in another database.
If it’s database specific, I can’t do anything w/o more information. Like, what’s different between those 2 databases? If you have the time, please file a Service Request with My Oracle Support.
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
I’m experiencing this in 4.1.5. Will we be seeing a fix in 4.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.
Sorry, are you connected to DB2?
It was my issue, it is working now. Thanks a lot.
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?
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?
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?
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!
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
Matt, your data grid does not look like this?
http://thatjeffsmith.wpengine.com/wp-content/uploads/2011/12/xml3.png
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…
try,
select xmltype.getclobval() from
@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.
@Marco – you had me at ‘if you like performance use the XMLType datatype in Oracle’ 🙂
Damn, I did it again. Next time I won’t mention the “performance” thingy anymore, so people keep focused on my text.
😎
Nice post Jeff, you refer a number of times to using an xml editor, i am quite comfotable with using Liquid XML Editor (http://www.liquid-technologies.com/xml-editor.aspx), which one do you prefer and can you offer a recommendation?
By no means is this an endorsement, but I’ve heard good things about Altova’s solution.