SQL - Tüm Entegrasyonlar Sorgusu EBIFlow

select * from
(
	select 
	        distinct
	        l.Name as 'Kutuphane',
	        p.Name as 'SurecAdi',
	        SurecVeri.Ent.value('(./@Function)[1]','nvarchar(max)') as Adı,
	        'BAPI' as Tipi
	from    wflProcesses p inner  join
	        wflLibraries l on l.Id=p.Library inner join
	        wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
	        pv.Graph.nodes('//BAPI') as SurecVeri(Ent)
	 
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            WebVeri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'WebInvokeNS' as Tipi
	from    wflProcesses p inner  join
                wflLibraries l on l.Id=p.Library inner join
                wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
                pv.Graph.nodes('//WebInvokeNS') as WebVeri(Ent)            
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'WebInvoke' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//WebInvoke') as Web2Veri(Ent)
    
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'JsonInvoke' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//JsonInvoke') as Web2Veri(Ent)
    
 UNION ALL 


	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlSelect' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlSelect') as Web2Veri(Ent)
    
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlExec' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlExec') as Web2Veri(Ent)
    
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlInsert' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlInsert') as Web2Veri(Ent)
    
 UNION ALL 

	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlUpdate' as Tipi
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            wflProcessVersions pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlUpdate') as Web2Veri(Ent)
) as Temp order by 1,2

select * from
(
	 select 
	         distinct
	         l.Name as 'Kutuphane',
	         p.Name as 'SurecAdi',
	         SurecVeri.Ent.value('(./@Function)[1]','nvarchar(max)') as Adı,
	         'BAPI' as Tipi
	 from    wflProcesses p inner  join
	         wflLibraries l on l.Id=p.Library inner join
	         (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
	         pv.Graph.nodes('//BAPI') as SurecVeri(Ent)
	 
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            WebVeri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'WebInvokeNS' as Tipi
	from    wflProcesses p inner  join
                wflLibraries l on l.Id=p.Library inner join
                (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
                pv.Graph.nodes('//WebInvokeNS') as WebVeri(Ent)
            
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'JsonInvoke' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//JsonInvoke') as Web2Veri(Ent)
 
    
 UNION ALL 


 	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Url)[1]','nvarchar(max)') as Adı,
            'WebInvoke' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//WebInvoke') as Web2Veri(Ent)
 
    
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlSelect' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlSelect') as Web2Veri(Ent)
    
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlExec' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlExec') as Web2Veri(Ent)
    
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlInsert' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlInsert') as Web2Veri(Ent)
    
 UNION ALL 

 
	select 
            distinct
            l.Name as 'Kutuphane',
            p.Name as 'SurecAdi',
            Web2Veri.Ent.value('(./@Connection)[1]','nvarchar(max)') as Adı,
            'SqlUpdate' as Tipi
 
    from    wflProcesses p inner  join
            wflLibraries l on l.Id=p.Library inner join
            (select Id, Process,TestMode, convert(xml,Graph) as Graph, Version, CreateDate, LastUpdate, Description from wflProcessVersions) pv on pv.Id=p.CurrentVersion cross apply
            pv.Graph.nodes('//SqlUpdate') as Web2Veri(Ent)
 
) as Temp order by 1,2