[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

RE: [xsl] SQL SELECT statement in XSLT!


Subject: RE: [xsl] SQL SELECT statement in XSLT!
From: "Michael Kay" <mhk@xxxxxxxxx>
Date: Wed, 6 Aug 2003 08:50:35 +0100

> I have three tables:ACCOUNTS, WACCTS and CURRENCIES...
> In ACCOUNTS i have a field CURR_CODE
> and
> In WACCTS i have a field CURR_ISO

You leave us to guess how these tables are represented in XML, but let's
suppose you've chosen to hold them as three separate documents that look
like:

<accounts>
  <account>
    <curr_code>1234</curr_code>
    <curr_iso>abcd</curr_iso>
    etc
  </account>
  <account>
    etc
  </account>
</accounts>

There are of course many other ways of representing tables in XML.
> 
> i want to write this select statement in XSLT:
> 
> "Select CURR_ISO from CURRENCIES where CURR_CODE(in 
> ACCOUNTS)=CURR_CODE(in 
> CURRENCIES)"
> i am using the ACCOUNTS table as the xml input to the 
> saxon... Do i have to merge ACCOUNTS and CURRENCIES XML 
> documents and use them as the 
> input or what???
> 

It's simplest to create a global variable that identifies each table:

<xsl:variable name="accounts"
select="document('accounts.xml')/accounts"/>
<xsl:variable name="currencies"
select="document('currencies.xml')/currencies"/>
<xsl:variable name="waccts" select="document('waccts.xml')/waccts"/>

Your select statement doesn't appear to use the third table.

It's a good idea to index the primary keys, or any other fields that
will be used in the join conditions:

<xsl:key name="account_key" match="account" select="account_number"/>
<xsl:key name="currency_key" match="currency" select="curr_code"/>

Then the join becomes:

<xsl:for-each select="$currencies/currency[key('account_key',
curr_code)]/curr_iso">

I probably haven't got this quite right because I can't quite reconcile
your description of your tables with the description of your query.

Michael Kay


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list



Current Thread
Keywords