Bad xml ascii char

0Hi People,

I am looking for some assistant with a script that I am writing, I have some data where spaces and bad BAD XML ASCII CHAR have been alowed into the system. I am trying to find all the bad char, however the script below I can only check one field at at time in this case' sText1' I would like it to loop though all the text fields in a table and inform me which field as the bad Char, and what position is and what ACSII code it is.

See below for example

these are the column headings

ColumnName :
job Desc

Position Bad ACSII :
41

InvalidCharacter:
ó

ASCIICode :
243

Text:
I would like to stay working in the Kraków, preferably in the computer sector. I would also like to develop my programming skills further .


DECLARE @colid AS INT;
DECLARE @string NVARCHAR(MAX)
DECLARE @position INT
DECLARE @ColumnName VARCHAR (MAX)
SET @colid = 0;

WHILE @colid < (SELECT MAX(column_id) FROM #cols)
BEGIN
--SELECT * FROM #cols
SELECT @ColumnName = column_id FROM #cols WHERE @ColumnName

SELECT @string=CAST(sText1 AS NVARCHAR(1000)) FROM dbo.tcandidate WHERE lid = 78030
(what to loop though all the lid id's and find which records and with in the record which cloumn as the bad data.)

SET @position = 1
WHILE @position <= DATALENGTH(@string)
BEGIN
IF ASCII(SUBSTRING(@string, @position, 1))<32
BEGIN
PRINT 'BAD XML ASCII CHAR:' + CAST(@position AS NVARCHAR(5)) +' '+ 'Column is:' + SUBSTRING(@string, @position, 1)
BREAK;
END

SET @position = @position + 1
END



SELECT @colid = min(column_id) FROM #cols WHERE @colid > column_id
END

--SELECT lID,sEmail1, sEmail2, sText1, sText2, sText3,sCVPath, sUDFText1,sUDF5 FROM tcandidate WHERE lid = 78030 -- these are the text field.

Any help would be much appreciated, just can't seem to work it out. I have tried amended the script and then it stops working