将msdb.dbo.sysjobsteps.command过滤到独立的列中

Question

我试图将msdb.dbo.sysjobsteps.command列拆分为不同的列，以显示以下信息：

• 文件夹
• 项目
• dtx封装

下面是我的数据的一个小样本：

/ISSERVER "\"\SSISDB\VWGroup_Packages\AudiME SSIS Continuous Load\DealerLoad_GX_FM_WW.dtsx\""
/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG IN SSIS Continuous Load\ABC to DW - Unapproved.dtsx\""
/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Submissions Load.dtsx\"" 
/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Validations Load.dtsx\"" 
/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco UK.dtsx\""
/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco ie.dtsx\"" 
/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG PL SSIS Continuous Load\ABC to DW - Approved.dtsx\"" 
/ISSERVER "\"\SSISDB\Yamaha Packages\Yamaha SSIS Packages and WareHouse_Loads\ABCtoDWLoadModifiedCnt.dtsx\""
/ISSERVER "\"\SSISDB\JLR\JLR SSIS Continuous Load\Submissions Load.dtsx\"" 

我尝试过使用substring方法，但是我似乎无法获得子字符串的起始和结束号。

我的目标是在ssms表中实现以下目标：

​

​

Answer 1

您可以使用将数据转换为xml来对反斜杠执行“拆分”，然后可以使用XQuery方法( MS上的更多信息这里 )提取所需的数据。

在下面的代码中，我用数据创建了一个名为@tmp的模拟表：

declare @tmp table (package_path nvarchar(max))

insert into @tmp values
     ('/ISSERVER "\"\SSISDB\VWGroup_Packages\AudiME SSIS Continuous Load\DealerLoad_GX_FM_WW.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG IN SSIS Continuous Load\ABC to DW - Unapproved.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Submissions Load.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Validations Load.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco UK.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco ie.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG PL SSIS Continuous Load\ABC to DW - Approved.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Yamaha Packages\Yamaha SSIS Packages and WareHouse_Loads\ABCtoDWLoadModifiedCnt.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\JLR\JLR SSIS Continuous Load\Submissions Load.dtsx\""')

;with splitted_packages
as (
    select 
        cast('<x>' + REPLACE(package_path, '\', '</x><x>') + '</x>' as xml) as package_frament
    from @tmp
    )
select
     package_frament.value(N'/x[4]', 'nvarchar(max)') as Folder
    ,package_frament.value(N'/x[5]', 'nvarchar(max)') as Projects
    ,package_frament.value(N'/x[6]', 'nvarchar(max)') as Package
from splitted_packages

这是前面命令的最终结果：

​

​