This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Hi,
What is the proper way to cast varchar value to XML which may contain illegal XML characters? Can you please explain with CDATA or should it be complex replace command?
Thanks in advance.
Can it be exemplified?
What do you mean? This was the code that broke because Address2 contained &:
SELECT
(
SELECT --if the billing address goes away entirely, every line will be a space instead of one empty <Address> node, maybe fix later
CAST(COALESCE(z.Address1, ' ') AS XML) Address1 -- space
, CAST(COALESCE(z.Address2, ' ') AS XML) Address2 -- space
, CAST(COALESCE(z.City, ' ') AS XML) City -- space
, CAST(COALESCE(z.State, ' ') AS XML) State -- space
, CAST(COALESCE(z.PostalCode, ' ') AS XML) PostalCode -- space
, CAST(COALESCE(z.County, ' ') AS XML) County -- space
FOR XML PATH('Address'), TYPE
Maybe like this: COALESCE(z.Address1, '') as Address1 etc. without CAST.
I think there was a reason to use the code like this. It's my colleague's code.
Then ask the colleague to try the suggestions.
The whole thing just looks wrong. Why would you cast an individual Address column to XML? That seems funny. And why would you replace a NULL address with a string like that? Yes, if you have in an XML document, it will be a space when you extract it. But your colleague does not have any XML at this point.
I think the snippet should be:
SELECT COALESCE(z.Address1, ' ') AS "@Address1",
COALESCE(z.Address2, ' ') AS "@Address2",
COALESCE(z.City, ' ') AS "@City",
COALESCE(z.State, ' ') AS "@State",
COALESCE(z.PostalCode, ' ')AS "@PostalCode",
COALESCE(z.County, ' ') AS "@County"
FOR XML PATH('Address'), TYPE
I've added @ before the column names to that the columns become attributes and not elements.