NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ
for more details.
I really like the Oracle XMLTABLE
feature, which allows you to treat an XML
string as if it were a real table. I've been using this feature quite a bit lately and had some difficulties initially around the default namespace so I thought I'd share my findings.
Lets say I had an XML
document that looked like this...
Now for the purposes of this article I will simply select this from dual
like below, however the same approach will work when the data is stored in a real table.
The result looks like this...
Now lets say I wanted my result to look like this instead...
you can. The trick is to set the default namespace
, otherwise no data will be returned. This only seems to be the case when the XML
query string is a non-root node
in my case. I found when selecting the root node
the default namespace doesn't matter.
The query below essentially converts values in the XML
document to columns that can be used with the rest of the SQL
Incidentally if the XML
was specifying the default xmlns
namespace instead of the xmlns:ik
namespace, the same XMLTABLE
query would work too.
A quick disclaimer...
Although I put in a great effort into researching all the topics I cover, mistakes can happen.
If you spot something out of place, please do let me know.
All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle).
Use of any information contained in this blog post/article is subject to this disclaimer
Other posts you may like...