使用Oracle数据库表中的自定义XML标签生成XML文件 [英] Generate XML file with Customized XML tags out of oracle database table
问题描述
我正在使用oracle数据库9ir2
我需要将一些表格转换成带有自定义格式标签的xml文件。
例如:
我想从emp表中的某些列生成XML,然后生成名为myxmlfile.xml的文件,如下所示:
< ;?xml version =1.0encoding =UTF-8?>
< entity-engine-xml>
< myxmlfile
EMPNO =8401
ENAME =JHON
HIREDATE =1988-12-30
SAL =5000
DEPTNO =10
/>
< myxmlfile
...
/>
< myxmlfile
...
/>
< / entity-engine-xml>
- 如何使用上述标签生成自定义XML文件,用户需要这样做,换句话说就是per user request,通过使用pl / sql,如何使用oracle的XML函数来输出自定义的期望的标签? 如何将xmltype转换为varchar2 ??? ... to_char()函数无法将XMLtype转换为char。
- 在客户端创建XML文件最简单的方法是什么?
注意:用户应用程序运行在XP PC上,由旧的oracle开发工具forms6i构建。 / div>
使用 XMLELEMENT
a> etc。$ b $ pre $
select xmlelement(entity-engine-xml,
xmlagg(
xmlelement (
myxmlfile,
xmlattributes(empno作为EMPNO,
作为ENAME),
to_char(hiredate,'yyyy-mm-dd')作为HIREDATE ,
sal为SAL,
为deptno为DEPTNO
)
)
)
).getclobval()
from emp;
。
如何将xmltype转换为varchar2?
theres a getStringVal 函数。即在我上面的例子看到我使用 getClobval
。还有一个 getstringval()
等值。
编辑:
假脱机:
在反馈上设置修剪off off off long 50000 lineize 32767 pagesize 0
col c format a32767
spool c:\temp\ foo.xml
select xmlelement(entity-engine-xml,
xmlagg(
xmlelement(
myxmlfile,
xmlattributes(empno asEMPNO,
作为ENAME,
to_char(hiredate,'yyyy-mm-dd')作为HIREDATE,
作为SAL,
作为DEPTNO
$ b).transform(xmltype('< xsl:stylesheet version =1.0
xmlns:xsl =http:// www .w3.org / 1999 / XSL / Transform>
< xsl:output omit-xml-decl aration =yesindent =yes/>
< xsl:template match =node()| @ *>
< xsl:copy>
< xsl:apply-templates select =node()| @ */>
< / xsl:copy>
< / xsl:template>
< / xsl:stylesheet>'))c
from emp;
假脱机
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.
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
这篇关于使用Oracle数据库表中的自定义XML标签生成XML文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!