SQL to extract Cadis Constructors

SQL to extract Cadis Constructor SQL

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
    , row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
 --, Definition as [DataPorter]
,Content.value('(Text)[1]', 'varchar(max)') as SourceSQL
,NULL SelectedProcess
,NULL DeleteTable
,NULL EmailSubject
,NULL ExecutableLocation
,NULL as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DC_CONSTRUCTION as comps on comps.Name = [Component Name]
CROSS APPLY [DEFINITION].nodes('/CadisXml/Content/Inputs/list/Item/RowSource/Select') AS Tbl(Content)
where [solution name] like '%'
and [component type] = 'DataConstructor'