Oracle XMLGEN and MII Query

[former_member1170711]

I have a case where I would like to build XML in Oracle using the XMLGEN, XML-SQL, and XMLDOM packages and then get the resultant XML in an MII Query. Is this possible? It looks like normally XMLGEN returns a CLOB which I am assuming will present a problem to MII (I am using 11.5). I am not sure if I could put it into a varchar and get it back that way or not. May be too small.

Has anyone ever successfully done this sort of thing?

We have a lot of SQL queries with logic in between. Oracle does this sort of thing very efficiently with PLSQL. MII transactions are very slow for this type of thing so we are wanting to put the logic in PLSQL where it makes sense.

Thanks,

Mike

[former_member1170711]

Hi Rick,

We are using the oracle-jdbc14.jar drivers with Oracle 10g. I created a test table with a CLOB column and then inserted a row with "this is a test" in the CLOB column. I then did select * from that table (fixed query template) and the resultant screen just showed "---" for the CLOB column. Apparently CLOB (even less than 4k) are not supported. If I use the same select statement in TOAD instead of in an MII query it shows the CLOB column contents correctly.

Thanks,

Mike

[former_member1170711]

It looks like we may not be using the latest drivers. I will try updating them and see if CLOBs are supported. Stay tuned.

-Mike

[former_member1170711]

This thread was a bit of a false alarm so I apologize for that.

After updating to the lastest version of the Oracle JDBC drivers CLOBs seem to work fine (even bigger than 4k). Furthermore, I am now able to generate XML in Oracle, return it as a CLOB in an MII query and then (in an MII transaction) assign the output of that query (the clob column) to an XML output transaction variable directly. At that point I can use the Runner to see the Oracle generated XML directly in a browser (or I can ship it to an AJAX component).

For anyone interested, here is a sample of what you can do in a fixed query to generate the XML and get it back as a CLOB.

select dbms_xmlgen.getxml('select * from yourTableHere') from dual

Oracle's XML format is a lot like the MII format. It has ROWSET and ROW tags but not ROWSETS.

This simple example is not particularly useful, however, you can use PLSQL and Oracle's XMLDOM package to build up whatever XML you need. I think there are some cases where this would be more efficient than trying to do the same thing in an MII transaction. I am thinking of cases where you need data from many select statements and you need to do a lot of processing of the various result sets before building the XML.

Thanks to Jeremy and Rick for the help.

-Mike

[former_member1170711]

Jeremy and Rick,

Thanks for your responses. We have a case where we are getting data from multiple selects, doing some intermediate processing , and then turning it into XML which then gets sent to an AJAX component. We are finding that this kind of thing can be very slow in MII (at least with 11.5) so we are starting to put more of the processing in PLSQL on the Oracle side.

As far as I know, there is no good way to get XML from Oracle to an MII transaction. I can't use the XML loader because I don't have a URL into Oracle to do this (and even if I did I don't want to be passing user/passwords). I think a CLOB column type is not an option in 11.5 is that correct? VARCHAR is too small to hold a large amount of XML.

I was thinking if I could have just got the XML back in a CLOB and then assigned that to a transaction's XML output property that might have been a reasonable way to go.

Our current plan to do all of the process except turning the data into XML in PLSQL and then let MII turn it into XML, apply a transform, and send it to the AJAX component.

Thanks for you input.

-Mike

[Former Member]

Depends on which Oracle JDBC Driver/version you're using. In later JDBC drivers (according to some stuff I read), the standard JDBC getString() method can be used on CLOB columns returned from a query. If so, the MII database connector should be able to get it.

All I can suggest is "try it". Convert the XML to a CLOB in a query and return it to MII, and see how much data you get back (if you get no data or it gets truncated at 4K characters, you'll know there's a problem). Make sure you use an XML document large enough to exceed that 4K limit.

[jcgood25]

The XMLQuery template will expect xml in Rowsets/Rowset MII format, or you will need to apply a transform to convert it. The XMLLoader action inside business logic will take any XML format.

I think you would be better off just creating your resultset in a procedure or function call on the Oracle side and return it in a standard way in either a FixedQuery template, or if you have a cursor then FixedQueryWithOutput mode. If you have to cast it to something other than a clob then it's probably still the better option.

Building up all sorts of xml, especially trying to replicate MII format XML from an outside source would not be my first recommendation. It will likely create headaches or maintenance issues. Is your resultset not in a tabular format?

[Former Member]

Other option would be to return it as a big VARCHAR or CLOB string from an SQLQuery and parse it in BLS using the StringToXml action, then do what you need to do with it.