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.
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(.,'"','''')"/> </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(.,'"',$apos)"/> </xsl:template> </xsl:stylesheet>
Source: Stackoverflow.com