XML

From: Roman Klin <rlklin_at_rambler.ru>
Date: 3 Oct 2004 22:49:38 -0700
Message-ID: <7868c7fc.0410032149.de91b6c@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 Mon Oct 04 2004 - 00:49:38 CDT