Home » SQL & PL/SQL » SQL & PL/SQL » XMLTYPE : CAST(MULTISET....) help ^^
icon10.gif  XMLTYPE : CAST(MULTISET....) help ^^ [message #184654] Thu, 27 July 2006 06:35 Go to next message
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
Junior Member

hi all,

can anyone help me?

here's the thing :
------------------
CREATE TABLE Basic(
ApplicationNo varchar2(10),
Domain varchar2(1),
Description varchar2(100)
constraint pk_basic PRIMARY KEY ApplicationNo);

CREATE TABLE Applicant(
ApplicationNo varchar2(10),
ApplicantName varchar2(30),
DueDate date
constraint fk_applicant FOREIGN KEY (ApplicationNo)
REFERENCES Basic(ApplicationNo) ENABLE);

CREATE TABLE Creator(
ApplicationNo varchar2(10),
CreatorName varchar2(40),
CreatorAddress varchar2(200)
constraint fk_creator FOREIGN KEY (ApplicationNo)
REFERENCES Basic(ApplicationNo) ENABLE);

i want to create an xml file with DBMS_XMLGEN, so i create xmltype :
--------------------------------------------------------------------
create or replace TYPE BASIC_TYP AS OBJECT(
"ApplicationNo" varchar2(10),
"Domain" varchar2(1),
Description varchar2(100));

create or replace TYPE APPLICANT_TYP AS OBJECT(
"ApplicantName" varchar2(120),
"DueDate" date);

create or replace TYPE ApplicantList_Typ AS TABLE OF APPLICANT_TYP;

create or replace TYPE CREATOR_TYP AS OBJECT(
"CreatorName" varchar(40),
"CreatorAddress" varchar2(200));

create or replace TYPE CREATORLIST_TYP AS TABLE OF CREATOR_TYP;

i 'expect' that my xml would look like this:
-----------------------------------------
<root>
<row>
<BasicTable>
<ApplicationNo>1111</ApplicationNo>
<Domain>A</Domain>
<Description>description no 1</Description>
</BasicTable>
<ApplicantTable>
<row>
<ApplicantName>John</ApplicantName>
<DueDate>20060806</DueDate>
</row>
<row>
<ApplicantName>David</ApplicantName>
<DueDate>20060301</DueDate>
</row>
<row>
<ApplicantName>Pricilla</ApplicantName>
<DueDate>20060104</DueDate>
</row>
</ApplicantTable>
<CreatorTable>
<row>
<CreatorName>Diana</CreatorName>
<CreatorAddress>Road No 1</CreatorAddress>
</row>
<row>
<CreatorName>Michael</CreatorName>
<CreatorAddress>Road No 2</CreatorAddress>
</row>
</CreatorTable>
</row>
</root>


so i create a function :
------------------------
procedure getXML(newContext_qry varchar2,filename varchar2)
is
begin
declare
qryCtx dbms_xmlgen.ctxHandle ;
result clob;
lob_length integer;
read_amount integer;
read_offset integer;

buffer varchar2(100);
loc varchar2(100) := 'usr_dir';
f_hand utl_file.file_type;

begin
-- Setting up offset and no. of chars to be read in
-- in one go from clob datatype.
read_offset := 1;
read_amount := 75;

dbms_output.put_line('opening');
--Opening file
f_hand := utl_file.fopen('ORALOAD',filename,'w');
dbms_output.put_line('file open');

-- Creating new context
qryCtx := dbms_xmlgen.newContext(
'SELECT BASIC_TYP(A.DOMAIN, A.APPLICATIONNO, A.DESCRIPTION) ' ||
' AS "BasicTable", ' ||
' CAST(MULTISET(SELECT B.APPLICANTNAME, B.DUEDATE ' ||
' FROM APPLICANT B ' ||
' WHERE B.APPLICATIONNO = A.APPLICATIONNO) ' ||
' AS APPLICANTLIST_TYP) AS "ApplicantTable", ' ||
' CAST(MULTISET(SELECT C.CREATORNAME, C.CREATORADDRESS ' ||
' FROM CREATOR C ' ||
' WHERE C.APPLICATIONNO = A.APPLICATIONNO) ' ||
' AS CREATORLIST_TYP) AS "CreatorTable" ' ||
' FROM BASIC A '|| newContext_qry);

-- Defining Rowsettag
DBMS_XMLGEN.setRowSetTag(qryCtx,'root');
-- Defining Rowtag
DBMS_XMLGEN.setRowTag(qryCtx,'row');

-- Null Handling Option
DBMS_XMLGEN.setNullHandling(qryCtx, 2);

-- Generating XML and storing in an clob datatype
result :=DBMS_XMLGEN.getXML(qryCtx);
dbms_output.put_line('xml generated');

-- Getting the length of the data stored in Clob
lob_length := dbms_lob.getlength(result);

-- Reading data from clob variable and writng into file.
while (lob_length > 0) loop
dbms_lob.read(result,read_amount,read_offset,buffer);

dbms_output.put_line('writing in file');
utl_file.put(f_hand,buffer);
dbms_output.put_line('written');
read_offset := read_offset+read_amount;
lob_length := lob_length-read_amount;
if lob_length < read_amount then
read_amount := lob_length;
end if;
end loop;
utl_file.fclose(f_hand);

EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

WHEN UTL_FILE.INVALID_OPERATION then
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');

WHEN UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');


WHEN UTL_FILE.WRITE_ERROR then
RAISE_APPLICATION_ERROR(-20104,'Write Error');

WHEN UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR(-20105,'Read Error');

WHEN UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(f_hand);
end;

end;


instead of what i expected, i have this result :
-----------------------------------
<root>
<row>
<BasicTable>
<ApplicationNo>1111</ApplicationNo>
<Domain>A</Domain>
<Description>description no 1</Description>
</BasicTable>
<ApplicantTable>
<APPLICANT_TYP> -----------> here's my problem T-T
<ApplicantName>John</ApplicantName>
<DueDate>20060806</DueDate>
</APPLICANT_TYP>
<APPLICANT_TYP>
<ApplicantName>David</ApplicantName>
<DueDate>20060301</DueDate>
</APPLICANT_TYP>
<APPLICANT_TYP>
<ApplicantName>Pricilla</ApplicantName>
<DueDate>20060104</DueDate>
</APPLICANT_TYP>
</ApplicantTable>
<CreatorTable>
<CREATOR_TYP> -----------> here's my problem T-T
<CreatorName>Diana</CreatorName>
<CreatorAddress>Road No 1</CreatorAddress>
</CREATOR_TYP>
<CREATOR_TYP>
<CreatorName>Michael</CreatorName>
<CreatorAddress>Road No 2</CreatorAddress>
</CREATOR_TYP>
</CreatorTable>
</row>
</root>


so, my problem is, i cannot rename APPLICANT_TYP and CREATOR_TYP into "row"
does Oracle support this?

i've tried to rename the tag :
CAST(MULTISET((SELECT B.APPLICANTNAME, B.DUEDATE
FROM APPLICANT B
WHERE B.APPLICATIONNO = A.APPLICATIONNO) AS "row")
AS APPLICANTLIST_TYP) AS "ApplicantTable"

but i get error missing right parentesis, etc...

i need to know, is it possible to rename multiset tag or not in Oracle?
if it is possible, can someone tell me how?
(^_________________^)

thanks


Re: XMLTYPE : CAST(MULTISET....) help ^^ [message #184681 is a reply to message #184654] Thu, 27 July 2006 07:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This thread on Ask Tom seems to be addressing the same problem.
Sean Dillons first response misses the mark a bit, but the followups after that seem to deal with the same problem.
Re: XMLTYPE : CAST(MULTISET....) help ^^ [message #184777 is a reply to message #184681] Thu, 27 July 2006 22:51 Go to previous messageGo to next message
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
Junior Member

it really works!!!

i use steps in FOLLOW-UP TO REVIEW 1

by using transform_xml_with_xsl function,
my xml came out perfectly just the way i want it to

thanks!!!! Laughing
Re: XMLTYPE : CAST(MULTISET....) help ^^ [message #435023 is a reply to message #184681] Sun, 13 December 2009 17:23 Go to previous messageGo to next message
tomtom
Messages: 1
Registered: December 2009
Junior Member
Hello

I was struggling with the same issue few days ago.
Finally found the new updated link from Ask Tom. Smile

asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1827993798549

Cheers!

[Updated on: Sun, 13 December 2009 18:05]

Report message to a moderator

Re: XMLTYPE : CAST(MULTISET....) help ^^ [message #435024 is a reply to message #435023] Sun, 13 December 2009 18:01 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1827993798549
Previous Topic: Find the Last Character
Next Topic: PL/SQL any on help me 4 thissss
Goto Forum:
  


Current Time: Wed Sep 28 17:40:55 CDT 2016

Total time taken to generate the page: 0.09202 seconds