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.
Hope you found this post useful...
...so please read on! I love writing articles that provide beneficial information,
tips and examples to my readers. All information on my blog is provided free of
charge and I encourage you to share it as you wish. There is a small favour I ask in return however -
engage in comments below, provide feedback, and if you see mistakes let me know.
If you want to show additional support and help me pay for web hosting and
domain name registration,
donations, no matter how small, are always welcome!
Use of any information contained in this blog post/article is subject to this disclaimer
Other posts you may like...