I've got the same problem:
Here's the XML file:
------------------------------------------------
<query>
<selectSet>
<select visible="N">
<columnName>arp r_field_oid</columnName>
<columnAlias>re sult_key</columnAlias>
<indexName>ARPR _FIELD281390</indexName>
<resourceOid> 0</resourceOid>
</select>
<select visible="Y">
<columnName>arp r_field_name</columnName>
<columnAlias>fi eld_name</columnAlias>
<indexName>ARPR _FIELD281390</indexName>
<resourceOid>20 00910</resourceOid>
</select>
<select visible="Y">
<columnName>d_a rpr_main_field_ name</columnName>
<columnAlias>ma in_field_name</columnAlias>
<indexName>ARPR _FIELD281390</indexName>
<resourceOid>20 01005</resourceOid>
</select>
<select visible="Y" translate="Y">
<columnName>arp r_field_type_cd </columnName>
<columnAlias>ar pr_field_type_c d</columnAlias>
<indexName>ARPR _FIELD281390</indexName>
<resourceOid>20 01032</resourceOid>
</select>
</selectSet>
<sortSet>
<sort>
<columnName>mai n_field_name</columnName>
</sort>
<sort>
<columnName>arp r_field_type_cd </columnName>
</sort>
<sort>
<columnName>fie ld_name</columnName>
</sort>
<sort>
<columnName>res ult_key</columnName>
</sort>
</sortSet>
<fromSet>
<from>
<tableName>arpr _field</tableName>
<indexName>ARPR _FIELD281390</indexName>
<alias>a</alias>
</from>
</fromSet>
<whereSet>
<where>
<indexFrom>ARPR _FIELD281390</indexFrom>
<columnFrom>del eted_flag</columnFrom>
<operator>=</operator>
<value>N</value>
<dataType>Chara cter</dataType>
</where>
<where upper="Y" wildcard="Y">
<indexFrom>ARPR _FIELD281390</indexFrom>
<columnFrom>arp r_field_name</columnFrom>
<operator>=</operator>
<value>gg</value>
<dataType>Varch ar2</dataType>
</where>
</whereSet>
</query>
------------------------------------------------
Here's the XSLT file:
------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSPY v2004 rel. 2 U (
http://www.xmlspy.com) by hairul
(iPerintis Sdn Bhd) -->
<xsl:styleshe et version="1.0"
xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
xmlns:search="I PSearch">
<xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"
omit-xml-declaration="ye s"/>
<xsl:key name="from_inde xname" match="/query/fromSet/from"
use="./indexName"/>
<xsl:param name="inWildcar d">*</xsl:param>
<xsl:param name="outWildca rd">%</xsl:param>
<xsl:param name="hint"/>
<xsl:template match="/">
<xsl:apply-templates select="./query"/>
</xsl:template>
<xsl:template match="query">
<xsl:apply-templates select="./selectSet"/>
<xsl:apply-templates select="./whereSet" mode="from"/>
<xsl:apply-templates select="./whereSet"/>
</xsl:template>
<xsl:template match="selectSe t">
<xsl:value-of select="'SELECT '"/>
<xsl:value-of select="concat( $hint,' ')"/>
<xsl:apply-templates select="./select"/>
</xsl:template>
<xsl:template match="select">
<xsl:variable name="alias">
<xsl:choose>
<xsl:when test="./columnAlias != ''">
<xsl:value-of select="concat( ' AS ',./columnAlias)"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="''"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:choose>
<xsl:when test="./functionName">
<xsl:value-of select="concat( ./functionName, ' (')"/>
<xsl:apply-templates select="./functionParamSe t"/>
<xsl:value-of select="concat( ')', $alias)"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="concat( key('from_index name',
../indexName)/alias, '.', ./columnName, $alias)"/>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="position( ) != last()">
<xsl:value-of select="','"/>
</xsl:if>
<xsl:value-of select="' '"/>
</xsl:template>
<xsl:template match="function ParamSet">
<xsl:apply-templates select="./functionParam"/>
</xsl:template>
<xsl:template match="function Param">
<xsl:variable name="quote">
<xsl:choose>
<xsl:when test="./dataType != 'Integer'">
<xsl:value-of select='"' "'/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="''"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:choose>
<xsl:when test="./indexName">
<xsl:value-of select="concat( key('from_index name',
../indexName)/alias, '.', ./columnName)"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="concat( $quote, ./value, $quote)"/>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="position( ) != last()">
<xsl:value-of select="', '"/>
</xsl:if>
</xsl:template>
<xsl:template match="whereSet " mode="from">
<xsl:choose>
<xsl:when test="count(./where) > 0">
<xsl:choose>
<xsl:when test="count(./where[./indexTo]) > 0">
<xsl:value-of select="'FROM '"/>
<xsl:apply-templates select="./where[./indexTo]" mode="from1"/>
<xsl:apply-templates select="./where[./indexTo]" mode="from2"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="concat( 'FROM ',key('from_ind exname',
../where/indexFrom)/tableName, ' ' ,key('from_inde xname',
../where/indexFrom)/alias,' ')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
</xsl:choose>
</xsl:template>
<xsl:template match="where" mode="from1">
<xsl:value-of select="'('"/>
</xsl:template>
<xsl:template match="where" mode="from2">
<xsl:variable name="tmp_value 1">
<xsl:if test="position( ) = 1">
<xsl:value-of select="concat( key('from_index name',
../indexFrom)/tableName, ' ' ,key('from_inde xname', ./indexFrom)/alias,'
')"/>
</xsl:if>
</xsl:variable>
<xsl:variable name="tmp_value 2">
<xsl:call-template name="wherex">
<xsl:with-param name="indexFrom ">
<xsl:value-of select="./indexFrom"/>
</xsl:with-param>
<xsl:with-param name="columnFro m">
<xsl:value-of select="./columnFrom"/>
</xsl:with-param>
<xsl:with-param name="outerjoin ">
<xsl:value-of select="./@outerjoin"/>
</xsl:with-param>
<xsl:with-param name="indexTo">
<xsl:value-of select="./indexTo"/>
</xsl:with-param>
<xsl:with-param name="columnTo" >
<xsl:value-of select="./columnTo"/>
</xsl:with-param>
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="concat( $tmp_value1,' ',$tmp_value2,' ) ')"/>
</xsl:template>
<xsl:template name="wherex">
<xsl:param name="indexFrom "/>
<xsl:param name="indexTo"/>
<xsl:param name="columnFro m"/>
<xsl:param name="columnTo"/>
<xsl:param name="outerjoin "/>
<xsl:choose>
<xsl:when test="$outerjoi n = 'Y'">
<xsl:value-of select="' LEFT JOIN '"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="' JOIN '"/>
</xsl:otherwise>
</xsl:choose>
<xsl:value-of select="concat( key('from_index name',
$indexTo)/tableName, ' ' ,key('from_inde xname', $indexTo)/alias,' ON
')"/>
<xsl:value-of select="concat( key('from_index name',
$indexFrom)/alias,'.',$colu mnFrom,' ',./operator,'
',key('from_ind exname', $indexTo)/alias,'.',$colu mnTo)"/>
</xsl:template>
<xsl:template match="whereSet ">
<xsl:choose>
<xsl:when test="count(./where[not(./indexTo)]) > 0">
<xsl:value-of select="'WHERE '"/>
<xsl:apply-templates select="./where[not(./indexTo)]"/>
</xsl:when>
</xsl:choose>
</xsl:template>
<xsl:template match="where">
<xsl:choose>
<xsl:when test="./operator = 'IS NULL' or ./operator = 'IS NOT
NULL'">
<xsl:value-of select="concat( key('from_index name',
../indexFrom)/alias, '.', ./columnFrom, ' ',./operator, ' ')"/>
</xsl:when>
<xsl:when test="./value">
<xsl:variable name="tmp_value ">
<xsl:call-template name="replace-substring">
<xsl:with-param name="text">
<xsl:value-of select="./value"/>
</xsl:with-param>
<xsl:with-param name="from">&am p;apos;</xsl:with-param>
<xsl:with-param name="to">'</xsl:with-param>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="value">
<xsl:choose>
<xsl:when test="./dataType='Integ er'">
<xsl:value-of select="$tmp_va lue"/>
</xsl:when>
<xsl:when test='substring (./value, 1, 1)="'"'>
<xsl:value-of select="$tmp_va lue"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select='concat( "'", $tmp_value, "'")'/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:choose>
<xsl:when test="./@truncate = 'Y'">
<xsl:value-of
select="concat( 'CONVERT(dateti me,CONVERT(varc har(20),',
key('from_index name', ./indexFrom)/alias, '.', ./columnFrom, ', 106))
',./operator, ' ', $value)"/>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkUppe r">
<xsl:with-param name="left" select="concat( key('from_index name',
../indexFrom)/alias, '.', ./columnFrom, ' ')"/>
<xsl:with-param name="value" select="$value"/>
<xsl:with-param name="operator" select="./operator"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkOute rJoin">
<xsl:with-param name="left" select="concat( key('from_index name',
../indexFrom)/alias, '.', ./columnFrom, ' ')"/>
<xsl:with-param name="value" select="concat( key('from_index name',
../indexTo)/alias, '.', ./columnTo)"/>
<xsl:with-param name="operator" select="./operator"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="position( ) != last()">
<xsl:value-of select="' AND'"/>
</xsl:if>
<xsl:value-of select="' '"/>
</xsl:template>
<xsl:template name="checkUppe r">
<xsl:param name="left"/>
<xsl:param name="value"/>
<xsl:param name="operator"/>
<xsl:choose>
<xsl:when test="./@upper = 'Y'">
<xsl:call-template name="checkGrou p">
<xsl:with-param name="left" select="concat( 'UPPER(',
normalize-space($left), ') ')"/>
<xsl:with-param name="value">
<xsl:call-template name="upper-case">
<xsl:with-param name="text" select="$value"/>
</xsl:call-template>
</xsl:with-param>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkGrou p">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="string( $value)"/>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="checkGrou p">
<xsl:param name="left"/>
<xsl:param name="value"/>
<xsl:param name="operator"/>
<xsl:choose>
<xsl:when test="./@group = 'Y' and $operator = '='">
<xsl:call-template name="checkWild card">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="concat( '(',
search:ConvertG roup($value), ')')"/>
<xsl:with-param name="operator" select="'IN'"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="./@group = 'Y' and $operator != '='">
<xsl:call-template name="checkWild card">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="concat( '',
search:ConvertG roup($value), '')"/>
<xsl:with-param name="operator" select="'NOT IN'"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkWild card">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="$value"/>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="checkWild card">
<xsl:param name="left"/>
<xsl:param name="value"/>
<xsl:param name="operator"/>
<xsl:choose>
<xsl:when test="./@wildcard = 'Y' and contains($value , $inWildcard)">
<xsl:call-template name="checkOute rJoin">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="transla te($value, $inWildcard,
$outWildcard)"/>
<xsl:with-param name="operator" >
<xsl:choose>
<xsl:when test="$operator = '='">LIKE</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="$operat or"/>
</xsl:otherwise>
</xsl:choose>
</xsl:with-param>
</xsl:call-template>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkOute rJoin">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="$value"/>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="checkOute rJoin">
<xsl:param name="left"/>
<xsl:param name="value"/>
<xsl:param name="operator"/>
<xsl:choose>
<xsl:when test="./@outerjoin = 'Y'">
<xsl:call-template name="checkOper ator">
<xsl:with-param name="left"
select="concat( 'ISNULL(',$left ,',',$value,')' )"/>
<xsl:with-param name="value" select="$value"/>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwis e>
<xsl:call-template name="checkOper ator">
<xsl:with-param name="left" select="$left"/>
<xsl:with-param name="value" select="$value"/>
<xsl:with-param name="operator" select="$operat or"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="checkOper ator">
<xsl:param name="left"/>
<xsl:param name="value"/>
<xsl:param name="operator"/>
<xsl:choose>
<xsl:when test="./operator = 'IN' or ./operator = 'NOT IN'">
<xsl:value-of select="concat( ' ', $left, $operator, ' (', $value,
')')"/>
</xsl:when>
<xsl:otherwis e>
<xsl:value-of select="concat( ' ', $left, $operator, ' ', $value)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="upper-case">
<xsl:param name="text"/>
<xsl:value-of select="concat( 'UPPER(', $text, ')')"/>
</xsl:template>
<xsl:template name="replace-substring">
<xsl:param name="text"/>
<xsl:param name="from"/>
<xsl:param name="to"/>
<xsl:choose>
<xsl:when test="contains( $text,$from)">
<xsl:value-of select="substri ng-before($text, $from)"/>
<xsl:copy-of select="$to"/>
<xsl:call-template name="replace-substring">
<xsl:with-param name="text" select="substri ng-after($text,$fr om)"/>
<xsl:with-param name="from" select="$from"/>
<xsl:with-param name="to" select="$to"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwis e>
<xsl:copy-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
------------------------------------------------
Here's the code:
------------------------------------------------
Private Function BuildSQL() As String
Const THIS_ROUTINE As String = "BuildSQL"
Dim objQuery As New XmlDocument
Dim objXSL As New Xsl.XslTransfor m
Dim swTemp As New IO.StringWriter
Dim objXMLNav As XPath.XPathNavi gator
Dim objXSLArgList As New Xsl.XsltArgumen tList
Dim szXSLFile As String
'LogTrace(IPLog Level.IPLogLeve lLow, m_szSessionID,
THIS_SUBSYSTEM, THIS_CLASS, THIS_ROUTINE, "Entry")
BuildSQL = ""
Try
objQuery.Load(" XMLFile.xml")
objXMLNav = objQuery.Create Navigator
objXSL.Load("XS LTFile.xsl")
objXSLArgList.A ddExtensionObje ct("IPSearch", Me)
objXSL.Transfor m(objXMLNav, objXSLArgList, swTemp, Nothing)
BuildSQL = swTemp.ToString ()
Catch
With Err()
.Raise(.Number, .Source, .Description)
End With
End Try
'LogTrace(IPLog Level.IPLogLeve lLow, m_szSessionID,
THIS_SUBSYSTEM, THIS_CLASS, THIS_ROUTINE, "Exit")
End Function
------------------------------------------------
I'm using .NET v1.1.
It will work in XMLSpy but not when with XSLTransform.
An MS bug?
Wan Yussman
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
