Generate XML file with Customized XML tags out of oracle database table
10,038
you do this with XMLELEMENT etc.
select xmlelement("entity-engine-xml",
xmlagg(
xmlelement(
"myxmlfile",
xmlattributes(empno as "EMPNO",
ename as "ENAME",
to_char(hiredate, 'yyyy-mm-dd') as "HIREDATE",
sal as "SAL",
deptno as "DEPTNO"
)
)
)
).getclobval()
from emp;
.
how to convert xmltype to varchar2 ???
theres a getStringVal function for this. i.e see in my example above i used getClobval
. there's a getstringval()
equivalent.
EDIT: spooling:
set trims on feedback off heading off long 50000 linesize 32767 pagesize 0
col c format a32767
spool c:\temp\foo.xml
select xmlelement("entity-engine-xml",
xmlagg(
xmlelement(
"myxmlfile",
xmlattributes(empno as "EMPNO",
ename as "ENAME",
to_char(hiredate, 'yyyy-mm-dd') as "HIREDATE",
sal as "SAL",
deptno as "DEPTNO"
)
)
)
).transform(xmltype('<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>')) c
from emp;
spool off
Author by
Eng. Samer T
Updated on June 07, 2022Comments
-
Eng. Samer T almost 2 years
I am working on oracle database 9ir2 I need to convert some tables into xml files with custom format tags.
for example: I want to generate XML from some columns in emp table then generate a file with name "myxmlfile.xml" as following:
<?xml version="1.0" encoding="UTF-8"?> <entity-engine-xml> <myxmlfile EMPNO="8401" ENAME="JHON" HIREDATE="1988-12-30" SAL="5000" DEPTNO="10" /> <myxmlfile ... /> <myxmlfile ... /> </entity-engine-xml>
- how to generate custom XML file with tags as above, and everytime the user need to do so, in other words "per user request" by using pl/sql, how to use oracle XML functions to output customized desired tags?.
- how to convert xmltype to varchar2 ??? ... to_char() function is unable to convert XMLtype to char.
- what is the easiest way to create XML file on client side ?
note: the user application is running on XP PCs, built by old oracle developer tools forms6i.
-
Eng. Samer T over 11 yearsyou answer is great, one thing left... how to write the above query result to xml file on client side?
-
Eng. Samer T over 11 yearsCurrnetly I am generate the file on server (to oracle database directory) and copy it to client by using ftp, is there otherway to generate the file directly on client side?
-
DazzaL over 11 years@Eng.SamerT using sqlplus you spool it out . see edit. the transform is just to indent the output.