ssis_t.TblParameter_src - V

CREATE VIEW [ssis_t].[TblParameter_src]
As
With
CTE_PkgLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      , pkg.PackageName
      , ParameterName     = cfnodes.x.value (
                                                'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]'
                                              , 'varchar(max)'
                                            )
      , ParameterHexValue = cfnodes.x.value (
                                                'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]'
                                              , 'varchar(max)'
                                            )
      , ExpressionValue   = cfnodes.x.value (
                                                'declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Expression[1]'
                                              , 'varchar(max)'
                                            )
      , ParameterQry      = cfnodes.x.query ( '.' ).query ( 'declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:PackageParameter/p1:Property' )
      , pkg.PackageCreatorName
    From
        ssis_t.pkgStats                                                                                                                           As pkg
        Cross Apply pkg.PackageXML.nodes ( 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:PackageParameters/*)' ) As cfnodes(x)
    )
--select * from CTE_PkgLevel
,
CTE_PkgVarLevel
As
    (
    Select
        pkg.RowID
      , pkg.PackagePath
      , pkg.PackageName
      , pkg.ParameterName
      , pkg.ExpressionValue
      , pkg.ParameterHexValue
      , ParameterValue    =
        (
            Select
                Top 1
                ParameterValue = cfnodes.x.value ( '.', 'nvarchar(max)' )
            From
                pkg.ParameterQry.nodes ( './*' ) As cfnodes(x)
        )
      --,pkg.ConnectionStringQry
      , ParameterDataType =
        (
            Select
                Top 1
                DataType = cfnodes.x.value (
                                               'declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:DataType[1]'
                                             , 'nvarchar(max)'
                                           )
            From
                pkg.ParameterQry.nodes ( './*' ) As cfnodes(x)
        )
      , pkg.PackageCreatorName
    From
        CTE_PkgLevel As pkg
    )
--Insert Into ssis.TblParameterDetails
--(
--    RowID
--  , PackagePath
--  , PackageName
--  , Category
--  , PackageCreatorName
--  , ObjectName
--  , ObjectType
--  , ObjectValue
--  , ExpressionValue
--  , ParameterHexValue
--)
Select
    Distinct
    Tblvar.RowID
  , Tblvar.PackagePath
  , Tblvar.PackageName
  --, PackageName     = Replace ( Tblvar.PackagePath, Replace ( @Path, '*.dtsx', '' ), '' )
  , Category          = 'Variable'
  , Tblvar.PackageCreatorName
  , ParameterName     = Cast(Tblvar.ParameterName As Varchar(Max))
  , ParameterDataType = Cast(Tblvar.ParameterDataType As Varchar(Max))
  , ParameterValue    = IsNull ( Cast(Tblvar.ParameterValue As Varchar(Max)), '' )
  , ExpressionValue   = IsNull ( Cast(Tblvar.ExpressionValue As Varchar(Max)), '' )
  , Tblvar.ParameterHexValue
From
    CTE_PkgVarLevel As Tblvar;