Thursday, May 10, 2012

I think this post xml query hungs up with large xml response from utl_http request on the OTN Forums and the response nicely sums up what I mentioned in my Options for Slow Performance Parsing Large XML series. If you want good performance for parsing large XML that is generated external to Oracle and you are on 11g then you need to INSERT the data into an XMLTYPE column stored as securefile binary xml and then query against that data.

XMLElement and namespaces

This post was inspired by the following thread in the OTN forum Formatting data to xml consumes more time. I did some Google searching and did not turn up any decent hits on the topic (or my searching was bad for the day) so I decided to do a quick write up on it.

The first place to start for learning how to generate XML data from an Oracle DB is of course the documentation. This can be found at Generating XML Data from the Database.

If you look through that document, there are not many references to namespaces and namespace prefixes. In fact, only Example 18-6 shows how to add a namespace to a node, but not how to assign a prefix to that node. I created the following example to show how to add a namespace and namespace prefix to nodes in XML.
SELECT XMLElement("t:global-instance",
                  XMLAttributes('your.namespace.uri.here' AS
                                  "xmlns:t"),
                  XMLElement("t:child1",'c1'),
                  XMLForest('c2' AS "t:child2",
                            'c3' AS "t:child3"),
                  XMLElement(evalname('t1:' || dummy), 'c4')).getClobVal() rslt 
  FROM dual;
which generates
<t:global-instance xmlns:t="http://your.namespace.here">
 <t:child1>c1</t:child1>
 <t:child2>c2</t:child2>
 <t:child3>c3</t:child3>
 <t1:X>c4</t1:X>
</t:global-instance>

That shows the basics for adding a prefix when using XMLElement and XMLForest. If you are using 10.2.0.3 or later, you can also use EvalName to generate the node name instead of hard-coding it as earlier versions required. In that case, I showed how to include the namespace prefix for EvalName as well. Yes it really is that simple. You just type in the prefix you want to use.