[sql] Generic XSLT Search and Replace template

I am trying to find (before re-inventing) a "simple" XSLT template that will take ANY xml document, find ALL text elements within that XML and replace all ' with ''. I'm doing a "SELECT ... FOR XML" from SQL and then "saving" that XML for later insertion back into SQL. After doing the SELECT FOR XML, I would like to make all ' ''s in that text before processing it further. That way, after being integrated into other XML (which may not need such a replacement) the text will already be "SQL Safe".

Any suggestions on how to best accomplish this would be greatly appreciated.

This question is related to sql xslt

The answer is

Here's one way in XSLT 2

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   <xsl:template match="@*|node()">     <xsl:copy>       <xsl:apply-templates select="@*|node()"/>     </xsl:copy>   </xsl:template>   <xsl:template match="text()">     <xsl:value-of select="translate(.,'&quot;','''')"/>   </xsl:template> </xsl:stylesheet> 

Doing it in XSLT1 is a little more problematic as it's hard to get a literal containing a single apostrophe, so you have to resort to a variable:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   <xsl:template match="@*|node()">     <xsl:copy>       <xsl:apply-templates select="@*|node()"/>     </xsl:copy>   </xsl:template>   <xsl:variable name="apos">'</xsl:variable>   <xsl:template match="text()">     <xsl:value-of select="translate(.,'&quot;',$apos)"/>   </xsl:template> </xsl:stylesheet>