Specific Problem with escaping quotes in XML/Xpath

Question

I have specific problem with some inbound messages that contains quotes(for example) 's-Hertogenbosch and i need to escape the first single quote because i can't inject whole node-set in the DataBase..can anyone tell me solution how can i ignore that quote?..or briefly how at all can i dynamically escape unnecessary quotes in every inbound message except first and last one.

I tryed with translate($InboundMessage, ' " ', "(blankspace)" ) and it worked fine but it removed all quotes..i need to keep first and last one..for example my Inbound message is

'Last year i visited 's-Hertogenbosch '..that single quote makes harm because on the output i got 'Last year i visited' without the name of the city.

Solution?

Regards.

Answer 1

Try this expression....

 <xsl:value-of select='replace($InboundMessage, "(.)&apos;+(.)", "$1$2")'/>

Or, if you want to do it for double quotes...

<xsl:value-of select="replace($InboundMessage, '(.)&quot;+(.)', '$1$2')"/>

EDIT: If you can only use XSLT1.0 and XPATH 1.0, then you have to do some string manipluation

<xsl:value-of select='concat(substring($InboundMessage, 1, 1), translate(substring($InboundMessage, 2, string-length($InboundMessage) - 2), "&apos;", ""), substring($InboundMessage, string-length($InboundMessage)))'/>

Alternatively, given your comment about building SQL, you could just replace all apostrophes, and put the outer apostrophes back in the SQL string you build

<xsl:value-of select='concat("INSERT INTO ", $Destination, " (", $Column, ") VALUES (&apos;", translate($InboundMessage, "&apos;", ""), "&apos;)")'/>