Venio Console: Duplicate File path is giving the error

Problem

When trying to populate the duplicate file path from the console, it throws up an error message saying the selected media for XML could not serialize the data for node 'No name' because it contains a character(0X0000) which is not allowed in XML.

Reference ticket #13379

Resolution

The cause of this issue was due to some illegal character for XML in relative file path , which we are replacing those characters  with empty characters . But in this case somehow there was error while converting to XML . So we provided the function which would replace Ascii less than 33 character with space character.

In order to resolve, we altered the following stored procedure and the function:

ALTER PROCEDURE [dbo].[sp_ex_ComputeDuplicateFilePath]
(
@UserId BIGINT,
@AddFileName BIT,
@SourceType NVARCHAR(50), --'MEDIA'/'TAG'
@SourceIdList NVARCHAR(MAX) -- comma seperated mediaid or tagid
)
AS
BEGIN

DECLARE @query NVARCHAR(MAX)


-- insert media ids or tag ids to the temp table
IF OBJECT_ID('tempdb..#docSources') IS NOT NULL
DROP TABLE #docSources

CREATE TABLE #docSources (Id int)

INSERT INTO #docSources
SELECT * FROM dbo.f_split(@SourceIdList, ',')


IF OBJECT_ID('tempdb..#tmpSourceFileTable') IS NOT NULL
DROP TABLE #tmpSourceFileTable

CREATE TABLE #tmpSourceFileTable ( FileId BIGINT PRIMARY KEY, CustodianId INT, MediaId INT, OriginalGroupId INT)

IF(@SourceType='MEDIA')
INSERT INTO #tmpSourceFileTable
SELECT FileId, CustodianId, MediaId, CASE IsDuplicate WHEN 0 THEN FileID ELSE OriginalGroupId END
FROM tbl_ex_FileInfo fi WITH(NOLOCK) INNER JOIN
#docSources s on fi.MediaId = s.Id
WHERE fi.isparent = 1
ELSE
INSERT INTO #tmpSourceFileTable
SELECT fi.FileId, CustodianId, MediaId, CASE IsDuplicate WHEN 0 THEN fi.FileId ELSE OriginalGroupId END
FROM tbl_ex_FileInfo fi WITH(NOLOCK)
INNER JOIN tbl_ex_FileTagAssociation fta WITH(NOLOCK)
ON fi.FileId = fta.FileID
INNER JOIN #docSources s
ON fta.TagID = s.Id
WHERE fi.isparent = 1

IF OBJECT_ID('tempdb..#familyGroup') IS NOT NULL
DROP TABLE #familyGroup

CREATE TABLE #familyGroup ( GroupId BIGINT PRIMARY KEY WITH(IGNORE_DUP_KEY=ON))
INSERT INTO #familyGroup
SELECT OriginalGroupId
FROM #tmpSourceFileTable

IF OBJECT_ID('tempdb..#dupFilePath') IS NOT NULL
DROP TABLE #dupFilePath

CREATE TABLE #dupFilePath (GroupId BIGINT PRIMARY KEY, DuplicateFilePath NVARCHAR(MAX))

INSERT INTO #dupFilePath (GroupId, DuplicateFilePath )
SELECT GroupId,
CASE WHEN CHARINDEX(';' + RelativeFilePath1, ';' + RelativeFilePath2) > 0
THEN RelativeFilePath2
ELSE RelativeFilePath1 + RelativeFilePath2
END AS DuplicateFilePath
FROM (
SELECT fi.GroupId
,ISNULL(NULLIF(
case when @AddFileName=1 then fi.RelativeFilePath else
REPLACE(
fi.RelativeFilePath,'\'+fi.FileName,'')
end

,'')+';'
,'') as RelativeFilePath1
, ISNULL((
SELECT NULLIF([dbo].[ReplaceASCII](RelativeFilePath2) ,'')+';'
from ( select distinct

case when @AddFileName=1 then F.RelativeFilePath else
REPLACE(
F.RelativeFilePath,'\'+F.FileName,'')
end

as RelativeFilePath2
FROM tbl_ex_FileInfo F WITH(NOLOCK) WHERE F.OriginalGroupId=fi.FileId AND F.IsParent=1
) rp
FOR XML PATH , TYPE).value('.[1]', 'nvarchar(max)'),'') as RelativeFilePath2

FROM
tbl_ex_FileInfo fi WITH(NOLOCK)
INNER JOIN #familyGroup fg
ON fi.FileId = fg.GroupId
WHERE fi.IsParent =1 AND
fi.OriginalGroupId = -1 and fg.GroupId!=147403
) T

BEGIN TRANSACTION
DECLARE @ComputeDate DATETIME =GETDATE()

DELETE D
FROM tbl_ex_DuplicateFilePath D,
#tmpSourceFileTable sf
WHERE D.GroupId=sf.FileId


INSERT INTO tbl_ex_DuplicateFilePath (CustodianId,MediaId,GroupId,DuplicateFilePath,LastUpdatedDate,UpdatedBy)
SELECT CustodianId,MediaId,sf.FileId,DuplicateFilePath,@ComputeDate ,@UserId
FROM #dupFilePath df
INNER JOIN
#tmpSourceFileTable sf
ON
df.GroupId = sf.OriginalGroupId


INSERT INTO tbl_lg_DuplicateFilePathPopulationLog(PopulatedBy,PopulatedOn,SourceType,SourceID)
SELECT @UserId,@ComputeDate,@SourceType, Id
FROM #docSources

COMMIT

IF OBJECT_ID('tempdb..#docSources') IS NOT NULL
DROP TABLE #docSources

IF OBJECT_ID('tempdb..#tmpSourceFileTable') IS NOT NULL
DROP TABLE #tmpSourceFileTable

IF OBJECT_ID('tempdb..#familyGroup') IS NOT NULL
DROP TABLE #familyGroup

IF OBJECT_ID('tempdb..#dupFilePath') IS NOT NULL
DROP TABLE #dupFilePath

END

And also the function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ReplaceASCII](@inputString VARCHAR(max)) RETURNS VARCHAR(max) AS BEGIN DECLARE @badStrings VARCHAR(100); DECLARE @increment INT= 1; WHILE @increment <= DATALENGTH(@inputString) BEGIN IF(ASCII(SUBSTRING(@inputString, @increment, 1)) < 33) BEGIN SET @badStrings = CHAR(ASCII(SUBSTRING(@inputString, @increment, 1))); SET @inputString = REPLACE(@inputString, @badStrings, case when len(@badStrings)>0 then replicate(' ',len(@badStrings)) else ' ' end); END; SET @increment = @increment + 1; END; RETURN @inputString; END