sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎tsql | XML‎ > ‎

sqlxml | UTF-8 -> 16

set nocount on
CREATE TABLE ##tbl (
id bigint
)
declare @id bigint, @xmlstr nvarchar(max), @xml xml

declare ani_cur cursor for
select ID, REPLACE(XML_COL, 'encoding="UTF-8"', 'encoding="UTF-16"')
  FROM TBL

open ani_cur
fetch next from ani_cur into @id, @xmlstr

while(@@FETCH_STATUS = 0)
begin

begin try 
select @xml = cast(@xmlstr as xml)
end try
begin catch
insert into ##tbl values (@id)
end catch

fetch next from ani_cur into @id, @xmlstr

end
close ani_cur
deallocate ani_cur

select count(1) from ##tbl
select * from ##tbl

drop table ##tbl
Comments