Data Porter Detail V2

-- 
-- Cubby's useful SQL
--
-- This extracts details from the Cadis Data Porter XML
-- Output:
-- porter sql
-- delete sql
--      output table name
--
 


Declare @PorterName varchar(250)
set @PorterName = '%' 

select comps.Name 
, row_number() over (partition by [Name] order by  [Name]) as seq
,dpref.value('Description[1]','nvarchar(max)') Descr
,dpref.value('Name[1]','nvarchar(max)') Name
,dpref.value('SourceDataSource[1]/Select[1]/Text[1]','nvarchar(max)') SourceSQL
,dpref.value('SourceConnection[1]/Name[1]','nvarchar(200)') SourceConnection
,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'
,dpref.value('Targets[1]/list[1]/Item[1]/TargetConnection[1]/Name[1]','nvarchar(max)') TargetConnection
,dpref.value('Targets[1]/list[1]/Item[1]/TargetDataSource[1]/Table[1]/Name[1]','nvarchar(max)') TargetTable
from CADIS_SYS.DP_DATAPORT as comps 
CROSS APPLY comps.DEFINITION.nodes('//CadisXml/Content/Inputs/list/Item') AS DP(dpref)
where comps.Name like @PorterName
order by seq