This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
transforming data from db to xml w/ xsl
- From: Yi Qin <yiqin at BPA dot Arizona dot EDU>
- To: XSL-List at lists dot mulberrytech dot com
- Date: Mon, 1 Apr 2002 22:52:29 -0700 (MST)
- Subject: [xsl] transforming data from db to xml w/ xsl
- Reply-to: xsl-list at lists dot mulberrytech dot com
i'm using Oracle XSU to transform data from a database to an XML document.
i'm having some problem with transforming elements that can have multiple
instances. i've created the following tables and view in an Oracle
database:
tables:
customer(customerpk, firstname, lastname)
address(addresspk, customerpk, streetnum, streetdir, streetname,
streettype)
job(jobpk, customerpk, company, title)
view:
cust_detail(customerpk, firstname, lastname, streetnum, streetdir,
streetname, streettype, company, title)
after creating these, i inserted some data into the tables:
insert into customer values (1, 'CHARLES', 'TUCKER');
insert into address(1, 1, 2140, 'E', 'SPEEDWAY', 'BL');
insert into address(2, 1, 105, 'W', 'BROADWAY', 'BL');
insert into job(1, 1, 'SKY INC', 'DBA');
i used the cust.xsl for transformation:
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="rowset">
<xsl:for-each
select="/rowset/row/customerpk[not(preceding::customerpk=.)]">
<xsl:sort select="."/>
<customer id="{.}">
<name>
<firstname><xsl:value-of select="/rowset/row/firstname"/></firstname>
<lastname><xsl:value-of select="/rowset/row/lastname"/></lastname>
</name>
<xsl:for-each select="/rowset/row[customerpk = current()]">
<address type="{addresstype}">
<xsl:copy-of select="streetnum|streetdir|streetname|streettype"/>
</address>
</xsl:for-each>
<xsl:for-each select="/rowset/row[customerpk = current()]">
<job>
<xsl:copy-of select="company/title"/>
</job>
</xsl:for-each>
</customer>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
at the command prompt, i typed the following:
java OracleXML getXML -user "test/test" -useLowerCase -setXSLT
"cust.xsl" "select *
from cust_detail" > cust.xml
here's the resulting the xml file:
<?xml version = '1.0'?>
<customer id="1">
<name>
<firstname>CHARLES</firstname>
<lastname>TUCKER</lastname>
</name>
<address>
<streetnum>2140</streetnum>
<streetdir>E</streetdir>
<streetname>SPEEDWAY</streetname>
<streettype>BL</streettype>
</address>
<address>
<streetnum>105</streetnum>
<streetdir>W</streetdir>
<streetname>BROADWAY</streetname>
<streettype>BL</streettype>
</address>
<job>
<company>SKY INC</company>
<title>DBA</title>
</job>
<job>
<company>SKY INC</company>
<title>DBA</title>
</job>
</customer>
as you can see, the job element appears twice even though the content is
the same for
both instances. does anyone know how to solve this problem?
thanks!
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list