Generate XML file using custom XML tags from oracle database table
I am working on 9ir2 oracle database I need to convert some tables to xml files with custom formatted tags.
eg: I want to create XML from some columns in the emp table and then generate a file named "myxmlfile.xml" like this:
<?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 create custom XML file with tags as above and every time user has to do so, in other words "per user request" with pl / sql, how to use oracle XML functions to output customized desired tags?
- how to convert xmltype to varchar2 ??? ... to_char () cannot convert XMLtype to char.
- what is the easiest way to create client side XML file?
Note: The custom application runs on XP machines built with the old oracle forms6i developer tools.
+3
source to share
1 answer
you do it 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 for this. In my example above, I used getClobval
. there is a getstringval()
equivalent.
EDIT: winding:
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
+3
source to share