Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: XML

Re: XML

From: KurtisK <KJKYLE_at_COOLBLUENOSPAM.COM>
Date: Wed, 27 Oct 2004 15:09:12 -0400
Message-ID: <MOqdnSsZsJXAb-LcRVn-pg@telcove.net>


This block shows how we do this in our application. We basically pass in a CLOB parameter to stored procedure. In the procedure, we append to this CLOB using

Oracle's XML functions. Hope this helps.

Kurt

xmlDoc XMLTYPE;
responseXML CLOB;

DBMS_LOB.createtemporary(responseXML, TRUE);

responseXML := '<?xml version ...>';

SELECT XMLAGG(XMLELEMENT("Equipment", XMLATTRIBUTES(EquipmentID AS "EquipmentID"),

                                            XMLELEMENT("DatasetID",
DatasetID),
                                            XMLELEMENT("LastModified",
LastModified)))
INTO xmlDoc
FROM tblEquipment;

IF xmlDoc IS NOT NULL

THEN DBMS_LOB.append(responseXML, xmlDoc.Getclobval);

END IF;

-- 


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"Roman Klin" <rlklin_at_rambler.ru> wrote in message
news:7868c7fc.0410032149.de91b6c_at_posting.google.com...

> I have SQL query:
> ==========================================================================
> with s1 as
> (
> select /*to_clob(*/
> XMLElement("ATTR", XMLAttributes('OPERTYPE' as "CODE",
> RPOData.OperType as "VALUE")) OPERTYPE,
> XMLElement("ATTR", XMLAttributes('OPERDATE' as "CODE",
> RPOData.OperDate as "VALUE")) OPERDATE,
> XMLElement("ATTR", XMLAttributes('BARCODE' as "CODE",
> RPOData.BarCode as "VALUE")) BARCODE,
> XMLElement("ATTR", XMLAttributes('COMMENT' as "CODE",
> 'I01.00;'||RPOData.StrComment||
> (case when COUNTRYFROM IS NOT NULL then
> ';CountryFrom='||RPOData.CountryFrom ELSE '' end)|| (case when
> INTERNUM IS NOT NULL then ';InterNum='||RPOData.InterNum ELSE ''
> end) as "VALUE")) COMMENT_,
> XMLElement("ATTR", XMLAttributes('POSTMARK' as "CODE"),
> XMLElement("VAL", XMLAttributes('1' as
> "INDEX", decode(bitand(RPOData.PostMark, 1), 1, 1,0) as "VALUE" )),
> XMLElement("VAL", XMLAttributes('2' as
> "INDEX", decode(bitand(RPOData.PostMark, 2), 2, 1,0) as "VALUE" )),
> XMLElement("VAL", XMLAttributes('10'
> as "INDEX", decode(bitand(RPOData.PostMark,512),512, 1,0) as "VALUE"
> ))
> ) PostMark,
> to_char(RPOData.OperDate, 'DD.MM.YYYY HH24:MI:SS') EXECDT --
> from table(pk_xxxexport.XXXExport('02/01/2004','03/01/2004')) RPOData
> where rownum < 100
> ), s2 as
> (
> select XMLElement("OPR", XMLAttributes('' as "REFER",
> '0' as "UNDO",
> s1.EXECDT as "EXECDT",
> trunc(to_date(s1.EXECDT, 'DD.MM.YYYY HH24:MI:SS' ), 'DD') as
> "DOPER"),
> OperType,
> OperDate,
> BarCode,
> COMMENT_,
> PostMark) OPR
> from s1
> )
> select
> to_clob((XMLElement("MSG" , XMLAttributes('EXPOPR' as
> "FORMAT",
> 'A' as "SENDER",
> 'B' as "RECIPIENT",
> to_char(SYSDATE, 'YYYY.MM.DD HH24:MI:SS') as "TIME"
> ) ,
> XMLAGG(value(e)))
> )) msg
> from s2, TABLE(XMLSequence(Extract(s2.OPR, '/'))) e
> ==========================================================================
>
> 1) How I get start XML tag (<?xml version ...>) without any other tags
> (SYS_XMLGEN will add <ROWSET> tag)?
>
> 2) How I can convert result of this query to CLOB? The to_clob
> function didn't work properly(raise ORA-19011) and getClobValue raise
> ORA-0600...
>
> Oracle 9.2.0.4.0
>
> Thank for help.
Received on Wed Oct 27 2004 - 14:09:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US