Generate XML file with Customized XML tags out of oracle database table

Question

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.

DazzaL · Accepted Answer · 2013-01-23 15:22:22Z

3

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

edited Jan 23, 2013 at 15:22

answered Jan 22, 2013 at 22:50

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eng. Samer T Over a year ago

you answer is great, one thing left... how to write the above query result to xml file on client side?

Eng. Samer T Over a year ago

Currnetly 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 a year ago

@Eng.SamerT using sqlplus you spool it out . see edit. the transform is just to indent the output.

Collectives™ on Stack Overflow

Generate XML file with Customized XML tags out of oracle database table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related