| How to convert CLOB(results of an SQL query) to XMLDocument [message #636926] |
Tue, 05 May 2015 19:38  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Hi,
I have a query
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') '
Here the values being passed are runtime values where in vTblName = a table and record_update_tms = a specific date.
Based on the above, it fetches 845 rows after which I am doing the following:
vTblName ctn_pub_cntl.table_name%TYPE;
vLastPubTms ctn_pub_cntl.last_pub_tms%TYPE;
vRecordLength NUMBER(6);
l_clob CLOB;
l_count PLS_INTEGER;
l_nMagicNumber NUMBER(6);
l_xmlRowCount NUMBER(6);
l_nNumberofTimes NUMBER(6);
l_nCount NUMBER(30);
parser xmlparser.Parser;
l_xmldoc xmldom.DOMDocument;
--parser Xmlparser.newParser;
l_clob1 CLOB;
l_xmltype XMLTYPE;
l_ctx DBMS_XMLGEN.CTXHANDLE;
vStrSqlQuery VARCHAR2(32767);
l_ctx:=DBMS_XMLGEN.NEWCONTEXT(vStrSqlQuery);
DBMS_XMLGEN.SETNULLHANDLING(l_ctx, 2);
l_clob:=DBMS_XMLGEN.GETXML(l_ctx);
--DBMS_OUTPUT.put_line(to_char(l_clob));
-- l_xmltype:=DBMS_XMLGEN.GETXML(l_ctx);
l_xmlRowCount:= DBMS_XMLGEN.GETNUMROWSPROCESSED(l_ctx);
l_nCount:= DBMS_LOB.getlength(l_clob);
I am passing DBMS_XMLGEN.GETXML into l_ctx and storing the whole results of the query as a clob.
My question is, how do I convert this clob into a XMLDocument object. Any answers will be appreciated. Many thanks in advance.
[Updated on: Tue, 05 May 2015 21:54] by Moderator Report message to a moderator |
|
|
|
| How to convert CLOB(results of an SQL query) to XMLDocument [message #636927 is a reply to message #636926] |
Tue, 05 May 2015 19:39  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Hi,
I have a query
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') '
Here the values being passed are runtime values where in vTblName = a table and record_update_tms = a specific date.
Based on the above, it fetches 845 rows after which I am doing the following:
vTblName ctn_pub_cntl.table_name%TYPE;
vLastPubTms ctn_pub_cntl.last_pub_tms%TYPE;
vRecordLength NUMBER(6);
l_clob CLOB;
l_count PLS_INTEGER;
l_nMagicNumber NUMBER(6);
l_xmlRowCount NUMBER(6);
l_nNumberofTimes NUMBER(6);
l_nCount NUMBER(30);
parser xmlparser.Parser;
l_xmldoc xmldom.DOMDocument;
--parser Xmlparser.newParser;
l_clob1 CLOB;
l_xmltype XMLTYPE;
l_ctx DBMS_XMLGEN.CTXHANDLE;
vStrSqlQuery VARCHAR2(32767);
l_ctx:=DBMS_XMLGEN.NEWCONTEXT(vStrSqlQuery);
DBMS_XMLGEN.SETNULLHANDLING(l_ctx, 2);
l_clob:=DBMS_XMLGEN.GETXML(l_ctx);
--DBMS_OUTPUT.put_line(to_char(l_clob));
-- l_xmltype:=DBMS_XMLGEN.GETXML(l_ctx);
l_xmlRowCount:= DBMS_XMLGEN.GETNUMROWSPROCESSED(l_ctx);
l_nCount:= DBMS_LOB.getlength(l_clob);
I am passing DBMS_XMLGEN.GETXML into l_ctx and storing the whole results of the query as a clob.
My question is, how do I convert this clob into a XMLDocument object. Any answers will be appreciated. Many thanks in advance.
I must add that I looked at an example which uses XMLDOM.getXMLTYPE (http://www.quest4apps.com/create-xml-using-dbms_xmldom/). However he creates a new DOM document from scratch. Is this what I am supposed to do as well? Is the approach different there than mine?
[Updated on: Tue, 05 May 2015 19:40] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to convert CLOB(results of an SQL query) to XMLDocument [message #636930 is a reply to message #636929] |
Tue, 05 May 2015 21:48 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
I had posted a question titled "How to convert CLOB(results of an SQL query) to XMLDocument". Someone (I presume BlackSwan, but not sure though) has changed the title of my question to "How to get the right information suggested about Date". Not sure who and why. I cannot even reply to BS as he has blocked me.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
