Remove xml encoding from string

Question

Hi,

I have a sting like below

declare @XML nvarchar(max) = '<?xml version="1.0" encoding="US-ASCII"?><Rem.Ace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> </Rem.Ace>';

i want to remove xml encoding details and need result as below string
<Rem.Ace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> </Rem.Ace>;

Note: the encoding will change dynamically sometimes it would be utf-8 or utf-16

Thanks

Answer 1

Maybe the string operations are suitable:

if @xml like N'<?xml%' 
   set @xml = ltrim(substring(@xml, charindex(N'?>', @xml) + 2, len(@xml)))

Answer 2

Hi @kasim mohamed ,

Very similar to Viorel's solution, just shorter.

SQL

DECLARE @xml NVARCHAR(MAX) =  
N'<?xml version="1.0" encoding="US-ASCII"?>  
<Rem.Ace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">;</Rem.Ace>';  
  
SET @xml = STUFF(@xml, 1, CHARINDEX('?>', @xml) + 1, '');  
  
-- test  
SELECT @xml;