How to replace multiple different text occurrences during a transformation

Question

I'm having difficulty in replacing text using a transformation (xslt 2.0). My XML files are big, so I'm illustrating them as:

<?xml version="1.0" encoding="utf-8"?>
<sets>
    <set>
        <query>
            <command>
                SELECT * from [A].[dbo].TableOne where id in (select id from [B].[dbo].TableTwo)
            </command>
        </query>
    </set>
    <set>
        <query>
            <command>
                SELECT * from [A].[dbo].TableOne where id not in (select id from [B].[dbo].TableTwo)
            </command>
        </query>
    </set>
</sets>

I want to replace a) [A] by [AAA] and b) [B] by [BBB]. Until now I am able to do one single replacement a) or b) but not both in the same pass. There is surely a way that I'm yet to learn!

In my XSL, these are some working fragments:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
    xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition"
    xmlns:my="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"
    xmlns:saxon="http://icl.com/saxon" extension-element-prefixes="saxon"
>

<!-- Duplicate all nodes and attributes -->
<xsl:template match="node()|@*">
    <xsl:copy>
        <xsl:apply-templates select="node()|@*"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="//command">
    <xsl:copy>
        <xsl:value-of select="replace(./text(),$pOldNameforA, $pNewNameforA)" disable-output-escaping="yes" />  
    </xsl:copy>
</xsl:template>

I would like to include also the replacement of

        <xsl:value-of select="replace(./text(),$pOldNameforB, $pNewNameforB)" disable-output-escaping="yes" />

I'm not figuring it out. How can I replace text more than once applying different matches? Thanks!

Answer 1

Just use the result of one replace as the input to the other replace. Also, I'm not sure what the reason for disable-output-escaping is, but making command a CDATA element might be a better option.

XML Input

<sets>
    <set>
        <query>
            <command>
                SELECT * from [A].[dbo].TableOne where id in (select id from [B].[dbo].TableTwo)
            </command>
        </query>
    </set>
    <set>
        <query>
            <command>
                SELECT * from [A].[dbo].TableOne where id not in (select id from [B].[dbo].TableTwo)
            </command>
        </query>
    </set>
</sets>

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" cdata-section-elements="command"/>
    <xsl:strip-space elements="*"/>

    <xsl:param name="pOldNameforA" select="'\[A\]'"/>
    <xsl:param name="pNewNameforA" select="'[AAA]'"/>
    <xsl:param name="pOldNameforB" select="'\[B\]'"/>
    <xsl:param name="pNewNameforB" select="'[BBB]'"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="command">
        <xsl:copy>
            <xsl:value-of select="replace(replace(.,$pOldNameforA,$pNewNameforA),
                $pOldNameforB,
                $pNewNameforB)"/>            
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

Output

<sets>
   <set>
      <query>
         <command><![CDATA[
                SELECT * from [AAA].[dbo].TableOne where id in (select id from [BBB].[dbo].TableTwo)
            ]]></command>
      </query>
   </set>
   <set>
      <query>
         <command><![CDATA[
                SELECT * from [AAA].[dbo].TableOne where id not in (select id from [BBB].[dbo].TableTwo)
            ]]></command>
      </query>
   </set>
</sets>

If you have a lot of replacements, you could have only 2 xsl:param's that are sequences and then use a recursive template to iterate over them.

It's more code up front, but any future update would just be to the xsl:param's (which could get passed in at runtime).

Example (produces the same results as above using the same input)

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" cdata-section-elements="command"/>
    <xsl:strip-space elements="*"/>

    <xsl:param name="oldNames" select="('\[A\]','\[B\]')"/>
    <xsl:param name="newNames" select="('[AAA]','[BBB]')"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="command[$oldNames]">
        <xsl:copy>
            <xsl:call-template name="replace">
                <xsl:with-param name="string" select="."/>
                <xsl:with-param name="old" select="$oldNames"/>
                <xsl:with-param name="new" select="$newNames"/>
            </xsl:call-template>
        </xsl:copy>
    </xsl:template>

    <xsl:template name="replace">
        <xsl:param name="string"/>
        <xsl:param name="old"/>
        <xsl:param name="new"/>
        <xsl:variable name="newString">
            <xsl:value-of select="replace($string,$old[1],$new[1])"/>            
        </xsl:variable>
        <xsl:choose>
            <xsl:when test="count($old) > 1">
                <xsl:call-template name="replace">
                    <xsl:with-param name="string" select="$newString"/>
                    <xsl:with-param name="old" select="$old[position() > 1]"/>
                    <xsl:with-param name="new" select="$new[position() > 1]"/>
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$newString"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

</xsl:stylesheet>

If there are no replacements, just make the 2 params empty sequences (again, could be passed in at runtime):

<xsl:param name="oldNames" select="()"/>
<xsl:param name="newNames" select="()"/>