Monday, November 9, 2009

Creating a Report using XML Query Data source

Creating a Report using XML Query Data source
===============================================

1.

CREATE TABLE HALIM (ID NUMBER,NAME VARCHAR2(10));

INSERT INTO HALIM ( ID, NAME ) VALUES ( 14, 'nazim');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 10, 'halim');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 11, 'nuri');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 12, 'halu');

COMMIT;

2.

CREATE DIRECTORY my_xml AS 'D:\MY_XML\';

3.

CREATE OR REPLACE PROCEDURE BEFTN.GENERATE_XML_TO_DIR
( p_filename in varchar2 default 'HALIM',
p_dir in varchar2 default 'MY_XML' )
IS
v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN;
BEGIN
---v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml, 'w' ); ----'.xml', w'
v_OutFile := utl_file.fopen( p_dir, p_filename||'.xsd', 'w' ); ----'.xml', w'
---for manually input if needed
--- utl_file.put_line(v_OutFile,'');
--- utl_file.put_line(v_OutFile,' ');
---------------------------------------------------------------------------------
v_QryCtx := DBMS_XMLQuery.newContext('SELECT * FROM HALIM');
--- DBMS_XMLQuery.setRowsetTag(v_QryCtx, 'halim');
--- DBMS_XMLQuery.setRowTag(v_QryCtx, 'halim');
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx,1); -- 1 for DTD,2 for Schema
DBMS_XMLQuery.closeContext(v_QryCtx);

while v_more loop --for eliminating version clause ?xml version=''1.0'
utl_File.Put(v_OutFile, v_xml); --Substr(v_Xml, 24, 32767));
if length(v_Xml) > 32767 then
v_Xml := substr(v_Xml, 32768);
else
v_More := FALSE;
end if;
end loop;

--- utl_file.put_line(v_OutFile,'
');
utl_file.fclose( v_OutFile );

Exception
when others then
utl_file.fclose( v_OutFile );
End;
/

4.

EXEC BEFTN.GENERATE_XML_TO_DIR;

5.
create a report with THIS D:\MY_XML\HALIM.XSD FILE.
USEING DATA SOURCE FROM XML DATA SOURCE.

Creating a Report using XML Query Data source

Creating a Report using XML Query Data source
===============================================

1.

CREATE TABLE HALIM (ID NUMBER,NAME VARCHAR2(10));

INSERT INTO HALIM ( ID, NAME ) VALUES ( 14, 'nazim');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 10, 'halim');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 11, 'nuri');
INSERT INTO HALIM ( ID, NAME ) VALUES ( 12, 'halu');

COMMIT;

2.

CREATE DIRECTORY my_xml AS 'D:\MY_XML\';

3.

CREATE OR REPLACE PROCEDURE BEFTN.GENERATE_XML_TO_DIR
( p_filename in varchar2 default 'HALIM',
p_dir in varchar2 default 'MY_XML' )
IS
v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN:=TRUE;
BEGIN
---v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml, 'w' ); ----'.xml', w'
v_OutFile := utl_file.fopen( p_dir, p_filename||'.xsd', 'w' ); ----'.xml', w'
---for manually input if needed
--- utl_file.put_line(v_OutFile,'');
--- utl_file.put_line(v_OutFile,' ');
---------------------------------------------------------------------------------
v_QryCtx := DBMS_XMLQuery.newContext('SELECT * FROM HALIM');
--- DBMS_XMLQuery.setRowsetTag(v_QryCtx, 'halim');
--- DBMS_XMLQuery.setRowTag(v_QryCtx, 'halim');
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx,1); -- 1 for DTD,2 for Schema
DBMS_XMLQuery.closeContext(v_QryCtx);

while v_more loop --for eliminating version clause ?xml version=''1.0'
utl_File.Put(v_OutFile, v_xml); --Substr(v_Xml, 24, 32767));
if length(v_Xml) > 32767 then
v_Xml := substr(v_Xml, 32768);
else
v_More := FALSE;
end if;
end loop;

--- utl_file.put_line(v_OutFile,'
');
utl_file.fclose( v_OutFile );

Exception
when others then
utl_file.fclose( v_OutFile );
End;
/

4.

EXEC BEFTN.GENERATE_XML_TO_DIR;

5.
create a report with THIS D:\MY_XML\HALIM.XSD FILE.
USEING DATA SOURCE FROM XML DATA SOURCE.