This is the mail archive of the xsl-list@mulberrytech.com mailing list .


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]
Other format: [Raw text]

transforming data from db to xml w/ xsl


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


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]