I'm working on a system that takes relational data from a sql server DB and uses SSIS to produce an XML extract using sql server 2005's 'FOR XML PATH' command and a schema. The problem lies with replacing the XML reserved characters.
'FOR XML PATH' is only replacing <, >, and &, not ' and ", so I need a way of replacing these myself. I've tried pre-processing the fields in the database to replace XML reserved characters with their entitised equivalents (e.g. & becomes &), but once these fields are used to construct XML using FOR XML the leading & is replaced with &, so I end up with &amp; where I should have &.
What I've tried so far is altering the element's contents after the XML has been constructed using XQuery inside SQL server like so:
DECLARE @data VARCHAR(MAX)
SET @data = CONVERT(VARCHAR(MAX), [my xml column].query(' data(/root/node_i_want)')
SELECT @data = [function to replace quotes etc](@data)
SET [my xml column].modify('replace value of (/root/node_i_want)[1] with sql:variable("@data")')
but I get the same problem.
Essentially, is there something wrong I'm doing with the above, or a way to tell FOR XML to entitise other characters, or something like that? Basically anything short of having to write a program to change the XML after it has been assembled in large batches and saved to files!