Dear All,
Good day...
Please assist how to download the XML file with schema with attributes for each column..
I have created program file for download XML file from database table...the program unit show like as below output...
<?xml version="1.0" encoding="US-ASCII"?>
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
<z:row EmployeeId="7566" Name="JONES" Job="MANAGER" HireDate="1981-04-02" Salary="2975"/>
<z:row EmployeeId="7788" Name="SCOTT" Job="ANALYST" HireDate="1982-12-09" Salary="3000"/>
<z:row EmployeeId="7902" Name="FORD" Job="ANALYST" HireDate="1981-12-03" Salary="3000"/>
<z:row EmployeeId="7369" Name="SMITH" Job="CLERK" HireDate="1980-12-17" Salary="800"/>
<z:row EmployeeId="7876" Name="ADAMS" Job="CLERK" HireDate="1983-01-12" Salary="1100"/>
</rs:data>
</xml>
But., I need to download like below., with attribute and schema...please advice for below..
<?xml version="1.0" encoding="US-ASCII"?>
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='EmployeeId' rs:number='5' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='Name' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='11'/>
</s:AttributeType>
<s:AttributeType name='Job' rs:number='7' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='15'/>
</s:AttributeType>
<s:AttributeType name='HireDate' rs:number='7' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='date' dt:maxLength='15'/>
</s:AttributeType>
<s:AttributeType name='Salary' rs:number='7' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='date' dt:maxLength='15'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row EmployeeId="7566" Name="JONES" Job="MANAGER" HireDate="1981-04-02" Salary="2975"/>
<z:row EmployeeId="7788" Name="SCOTT" Job="ANALYST" HireDate="1982-12-09" Salary="3000"/>
<z:row EmployeeId="7902" Name="FORD" Job="ANALYST" HireDate="1981-12-03" Salary="3000"/>
<z:row EmployeeId="7369" Name="SMITH" Job="CLERK" HireDate="1980-12-17" Salary="800"/>
<z:row EmployeeId="7876" Name="ADAMS" Job="CLERK" HireDate="1983-01-12" Salary="1100"/>
</rs:data>
</xml>
-----------------------------------Please find the below program unit -----------------------------------------------
declare
v_edi_clob CLOB DEFAULT empty_clob();
v_clob clob;
v_xml xmltype;
cursor cjmst is
select xmlelement("xml" ,
xmlattributes('uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' as "xmlns:s",
'uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' as "xmlns:dt",
'urn:schemas-microsoft-com:rowset' as "xmlns:rs",
'#RowsetSchema' as "xmlns:z"),
xmlelement("rs:data" ,
(select xmlagg( xmlelement("z:row" ,XMLATTRIBUTES(
e.empno AS "EmployeeId",
e.ename AS "Name",
e.job AS "Job",
TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "HireDate",
e.sal AS "Salary",
e.comm AS "Commission"
)
)
)
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.deptno = 20
)
)
) jmst_xml
from dual jmst;
rjmst cjmst%rowtype;
v_jedi_rid attachment.attachment_id%type;
v_blob blob;
begin
rjmst := null;
open cjmst;
fetch cjmst into rjmst;
close cjmst;
v_xml := rjmst.jmst_xml;
v_clob := v_xml.getClobVal();
v_blob := v_xml.getBlobval(1);
v_jedi_rid := habl_attachment_id.nextval;
INSERT
INTO ATTACHMENT
(
ATTACHMENT_ID,
ATTACHMENT_RECEIVED_ON,
ATTACHMENT_NAME,
ATTACHMENT_CONTENT,
FILENAME,
MIMETYPE,
NOTE
)
VALUES
(
v_jedi_rid,
sysdate,
'Employee'||TO_CHAR(sysdate, 'DDMMYYHH24MISS')||'.xml',
v_blob,
'Employee'||TO_CHAR(sysdate, 'DDMMYYHH24MISS')||'.xml',
'text/xml',
'test uthaman'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, sqlerrm);
END;