Convert string to XML illegal characters

问题:

I'm trying to convert a string into XML and running into an illegal character issues when the string contains & symbol

select convert(xml, 
    '<root>
        <stuff>
            <test>something & this will error</test>
        </stuff>
    </root>')

XML parsing: line 3, character 25, illegal name character

回答1:

You need to escape the ampersand & character in XML. Try replacing the & with &

select convert(xml, 
    REPLACE('<root>
        <stuff>
            <test>something & this will error</test>
        </stuff>
    </root>','&','&amp;'))