SQL to Extract Data Porters

SQL to extract Data Porters - useful for impact analysis

with cte_soln as (SELECT [SolutionName] AS [Solution Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentName]
  ELSE RuleBuilderName 
 END AS [Component Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentType]
  WHEN RuleBuilderName is not null THEN 'RuleBuilder'
 END AS [Component Type]
, [Description]
, [xml]
FROM (
 SELECT nref.query('.') as [xml],
 nref.value('../../../Name[1]', 'nvarchar(250)') [SolutionName],
 nref.value('ProcessSpecification[1]/ProcessName[1]', 'nvarchar(500)') [ComponentName],
 nref.value('ProcessSpecification[1]/ComponentType[1]', 'nvarchar(500)') [ComponentType],
 nref.value('RuleBuilderName[1]', 'nvarchar(500)') RuleBuilderName,
 nref.value('Description[1]', 'nvarchar(4000)') [Description]
 FROM CADIS_SYS.CO_SOLUTION CROSS APPLY DEFINITION.nodes('//Content/Items/list/Item') AS R(nref)
 WHERE OBSOLETE=0 ) VW)
select [Solution Name] as SolutionName,[Component Name] as ComponentName
 ,[Component Type] as ComponentType
-- , Definition as [DataPorter]
, row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
,dpref.value('SourceDataSource[1]/Select[1]/Text[1]','nvarchar(max)') SourceSQL
,dpref.value('Custom[1]/SelectedProc[1]/Name[1]','nvarchar(200)') SelectedProcess
,dpref.value('DeleteDataSource[1]/Table[1]/Name[1]','nvarchar(200)') DeleteTable
,dpref.value('Email[1]/Subject[1]','nvarchar(200)') EmailSubject
,dpref.value('External[1]/FileName[1]','nvarchar(200)') ExecutableLocation
,dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DP_DATAPORT as comps on comps.Name = [Component Name]
CROSS APPLY DEFINITION.nodes('//CadisXml/Content/Inputs/list/Item') AS DP(dpref)
where [solution name] like '%'
and dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') is null