[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: Fri, 8 Aug 2003 12:11:23 +0100

> This is the source xml file:
> 
> <?xml version="1.0" encoding="utf-8" ?>
> <BBSD_CUST_ACCOUNTS>
> <mappings Record="1">
>   <BRCH_CODE>1</BRCH_CODE>
>   <CACC_NUM>TTTT/CACC_NUM>
>   <CIRT_CODE>016</CIRT_CODE>
>   <CURR_CODE>117</CURR_CODE>
>   <CUST_ID>0001</CUST_ID>
>   <ECOS_CODE>876</ECOS_CODE>
>   <CACC_B_NAME>zzzzzz</CACC_B_NAME>
>   <CACC_AVAIL_BAL>1548000</CACC_AVAIL_BAL>
>   </mappings>
>   <mappings>
>   etc...
>   </mappings>
>   </BBSD_CUST_ACCOUNTS>
> 
> This is the relevant XSLT:
> 
> <?xml version='1.0' encoding='utf-8' ?>
> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
> version="1.0">
> <xsl:output method="xml"/>
> <xsl:template match="/">
> <xsl:element name="WACCOUNTS">
> <xsl:for-each select="//BBSD_CUST_ACCOUNTS/mappings">

Don't use //BBSD_CUST_ACCOUNTS - that searches every element in the
document to see if it is a BBSD_CUST_ACCOUNTS. There is only one such
element, and it can be accessed very quickly as /BBSD_CUST_ACCOUNTS.

> <xsl:element name='{name()}'>
> <CURR_ISO>
> <xsl:value-of select="document('BBSC_CURRENCIES.xml')/BBSC_CURRENCIES
> 				/mappings/CURR_ISO"/>
> </CURR_ISO>
> </xsl:element>
> </xsl:for-each>
> </xsl:element>
> </xsl:template>
> </xsl:stylesheet>
> 
> 
> I am going to put the select statement here:
> select CURR_ISO from BBSC_CURRENCIES where CURR_CODE(in 
> BBSD_CUST_ACCOUNTS)=CURR_CODE(in BBSC_CURRENCIES)

The simplest is to write:

<xsl:value-of select="document('BBSC_CURRENCIES.xml')/BBSC_CURRENCIES
 		/mappings[CURR_CODE=current()/CURR_CODE]/CURR_ISO"/>

But it would be much more efficient to use a key, as previously
suggested:

<xsl:key name="k" 
         match="/BBSC_CURRENCIES/mappings"
         select="CURR_CODE"/>

and then you can write:

<xsl:variable name="code" select="CURR_CODE"/>
<xsl:for-each select="document('BBSC_CURRENCIES.xml')">
  <xsl:value-of select="key('k', $code)"/>
</xsl:for-each>

The xsl:for-each here isn't iterating, it's just there to establish the
right context node for the key() function.

Michael Kay

P.S. What are the eleven exclamation marks supposed to signify?

> 
> The output should be like this:
> <?xml version="1.0" encoding="utf-8" ?>
> <WACCOUNTS>
> <mappings>
>   <CUR_ISO>(VALUE FROM 'THE SELECT' STATEMENT ABOVE)</CUR_ISO>
>   </mappings>
> <mappings>
> etc..
> </mappings>
> </WACCOUNTS>
> 
> Thanks a lot...
> Michel
> 
> _________________________________________________________________
> Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
> http://join.msn.com/?page=features/junkmail
> 
> 
>  XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
> 


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



Current Thread
Keywords
xml