sql - 将 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 表中的以下内容：

标签： sqlsql-servertsqlsplit

Answer 1

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

在以下代码中，我创建了一个@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

这是上一条命令的最终结果：