[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

RE: Query Format in XML

• To: "'Philippe Poulard'" <Philippe.Poulard@s...>,"'Florent Georges'" <darkman_spam@y...>
• Subject: RE: Query Format in XML
• From: "Michael Kay" <mike@s...>
• Date: Fri, 19 May 2006 17:24:36 +0100
• Cc: <xml-dev@l...>
• In-reply-to: <446DDC98.3050402@s...>
• Thread-index: AcZ7VJHJCKri403dQZiclelRgnLe1wABwfAw

> 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/

• References:
  • Re: Query Format in XML
    • From: Philippe Poulard <Philippe.Poulard@s...>

• Prev by Date: RE: Implementing XML Includes in DTD
• Next by Date: Extending a Schema Definition
• Previous by thread: Re: Query Format in XML
• Next by thread: Re: Query Format in XML
• Index(es):
  • Date
  • Thread