Here is Krasavita's code formatted using SQLPrompt, so my be easier to read:

select 'DSPN'     = ltrim(rtrim([SPLIT].[a].[value]('.', 'VARCHAR(100)')))
  , 'DSPN_old'   = [a].[DSPN]
  , [a].[DSPNXML]

Well, Monday night is done and I managed another victory.

Does the stored procedure return data when you run in SSMS?

I would change this line:
If @searchstring <> '''' or @searchstring <> null
to this:
--If @searchstring <> '''' or @searchstring <> null
if @searchstring > ''

Does this run for you in SSMS?

--CREATE PROCEDURE [dbo].[usp_test]
declare
@year char(4) = '2017',
@criteria varchar(50) = NULL,
@Flag char(1)...