BAD XML ASCII CHAR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BAD XML ASCII CHAR

Author

Topic

mec01
Starting Member

2 Posts

Posted - 2011-06-24 : 03:27:30

Hi 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 fix this, at the moment I have to put in the text column by name and it will check the one column for the characters. I would like it to loop though all the text fields and and id'd (which the primary key is lid)

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.

Thanks inadvance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-24 : 07:50:53

You may be able to adapt the script/stored proc in this blog: http://beyondrelational.com/blogs/naomi/archive/2010/10/29/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

mec01
Starting Member

2 Posts

Posted - 2011-06-24 : 07:55:29

Thanks I will take a look.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources