Yeah, we should start think about it here, thank you. The problem is that we're using that on the return of a XML. The error are on the XML return. to solve, before call the function we got to use this.
SET CONCAT_NULL_YIELDS_NULL ON;
GO
This is our function
CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML
IF @BreakLine = 1
BEGIN
SET @Text= REPLACE(REPLACE(REPLACE(@Text,'
', CHAR(13)), '
',CHAR(13)), '
',CHAR(13))
END
ELSE
BEGIN
SET @Text= REPLACE(REPLACE(@Text, '
','
'), '
','
')
END
SET @XML = CAST(('' + REPLACE(REPLACE(dbo.Function\_RemoveInvalidCharacters(@Text), '&', '&'), '<', '')+'') AS XML)
SELECT
@Text=
(
SELECT
CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
'<' + N.value('.', 'VARCHAR(MAX)')
ELSE
STUFF
(
N.value('.', 'VARCHAR(MAX)')
, 1
, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
, ''
)
END
FROM
@XML.nodes('x') AS T(N)
FOR XML PATH('')
, TYPE
).value('.', 'VARCHAR(MAX)')
RETURN LTRIM(RTRIM(@Text))
END
GO
I'm sorry if the code looks confuse, was made for a coworker here. :-O
