[
Lists Home |
Date Index |
Thread Index
]
> can you please give me the stylesheet that can produce the
> following XML :
> <?xml version="1.0" encoding="UTF-8"?>
> <order id="1234">
> <dollar>
> <item part-number="321" price="138.95" quantity="1">
> Lawnmower
> </item>
> <item part-number="654" price="29.99" quantity="2">
> Baby monitor
> </item>
> </dollar>
> <euro>
> <item part-number="987" price="11.27" quantity="3">
> Roquefort Cheese
> </item>
> </euro>
> </order>
>
> ...from this SQL query :
> SELECT id,po.pn,qty,product,price,currency
> FROM po, products
> WHERE po.pn = products.pn
> AND id = ?
> ORDER BY currency
>
> ...with XSLT ?
The Saxon solution to your problem is:
<xsl:stylesheet
xmlns:sql="http://saxon.sf.net//extensions/net.sf.saxon.sql.SQLElementFactor
y"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; version="2.0"
xmlns:saxon="http://saxon.sf.net/";
extension-element-prefixes="saxon sql">
<!-- insert your database details here, or supply them in parameters -->
<xsl:param name="database" select="'jdbc:odbc:potestdb'"/>
<xsl:param name="user"/>
<xsl:param name="password"/>
<xsl:param name="purchase-order" select="'1234'"/>
<xsl:template name="main">
<xsl:if test="not(element-available('sql:connect'))">
<xsl:message>sql:connect is not available</xsl:message>
</xsl:if>
<xsl:message>Connecting to <xsl:value-of
select="$database"/>...</xsl:message>
<xsl:variable name="connection" as="java:java.sql.Connection?"
xmlns:java="http://saxon.sf.net/java-type";>
<sql:connect database="{$database}" user="{$user}"
password="{$password}"
driver="sun.jdbc.odbc.JdbcOdbcDriver"
xsl:extension-element-prefixes="sql">
<xsl:fallback>
<xsl:message terminate="yes">SQL extensions are not
installed</xsl:message>
</xsl:fallback>
</sql:connect>
</xsl:variable>
<xsl:message>Connected...</xsl:message>
<xsl:variable name="results">
<sql:query connection="$connection"
table="po, products"
column="id, po.pn, qty, product, price, currency"
where="po.pn = products.pn and id =
'{$purchase-order}'"/>
</xsl:variable>
<xsl:for-each-group select="$results/row" group-by="col[1]">
<order id="{current-grouping-key()}">
<xsl:for-each-group select="current-group()" group-by="col[6]">
<xsl:element name="{current-grouping-key()}">
<xsl:for-each select="current-group()">
<item part-number="{col[2]}" price="{col[5]}"
quantity="{col[3]}">
<xsl:value-of select="col[4]"/>
</item>
</xsl:for-each>
</xsl:element>
</xsl:for-each-group>
</order>
</xsl:for-each-group>
</xsl:template>
</xsl:stylesheet>
It seems to be about the same length as yours (and could be trimmed
further), and has the advantage that it only uses proprietary tags to do
things that are outside the scope of the standard.
Michael Kay
http://www.saxonica.com/
|
