Imagine this:
You want to make sure lines aren't longer than x characters to avoid automatic line breaks when printing from an sql database table to a form. We can warn users by using raiseerror.
DECLARE @xml XML
declare @input varchar(max) = :positionstext
SET @xml = CAST('<col>' + REPLACE(REPLACE(@input, CHAR(10), ''),CHAR(13),'</col><col>') + '</col>' AS XML)
-- create a cursor for looping
DECLARE @col VARCHAR(max)
DECLARE @i INT = 0;
declare cur cursor local fast_forward for
select
m.c.value('(text())[1]', 'nvarchar(max)') as col
from @xml.nodes('/col') as m(c)
open cur
while 1 = 1
begin
fetch cur into @col
if @@fetch_status <> 0 break
SET @i += 1;
DECLARE @warning VARCHAR(max) = 'Warning. Please check line ' + cast(@i as varchar)
IF(LEN(@col) > 85) -- change number of characters here
RAISERROR (@warning, 16, 0);
end
close cur
deallocate cur
-- This is here to avoid an error
select
m.c.value('(text())[1]', 'nvarchar(max)') as col
from @xml.nodes('/col') as m(c)