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