Filter msdb.dbo.sysjobsteps.command into seperate columns

Question

I am trying to split the msdb.dbo.sysjobsteps.command column into separate columns to show the following information: folder project dtx package A small sample of my data is below: I have tried to use the substring method, however I cannot seem to get the starting and ending number of the substring. My aim is for the following in a ssms table:

Accepted Answer

You can use transform your data to xml to perform a “split” on backslashes and then you can extract only the data you need with an XQuery using the value() xml method (more info here on MS Docs).In the following code I created a mock table called @tmp with your data:declare @tmp table (package_path nvarchar(max))insert into @tmp values ('/ISSERVER ""SSISDBVWGroup_PackagesAudiME SSIS Continuous LoadDealerLoad_GX_FM_WW.dtsx""') ,('/ISSERVER ""SSISDBVWGroup_PackagesVWG IN SSIS Continuous LoadABC to DW - Unapproved.dtsx""') ,('/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadSubmissions Load.dtsx""') ,('/ISSERVER ""SSISDBMercedes_PackagesMercedes Cars SSIS Continuous LoadValidations Load.dtsx""') ,('/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco UK.dtsx""') ,('/ISSERVER ""SSISDBAGCO_Packagesagco SSIS Continuous LoadMetis to ABC - agco ie.dtsx""') ,('/ISSERVER ""SSISDBVWGroup_PackagesVWG PL SSIS Continuous LoadABC to DW - Approved.dtsx""') ,('/ISSERVER ""SSISDBYamaha PackagesYamaha SSIS Packages and WareHouse_LoadsABCtoDWLoadModifiedCnt.dtsx""') ,('/ISSERVER ""SSISDBJLRJLR SSIS Continuous LoadSubmissions Load.dtsx""');with splitted_packagesas ( select cast('' + REPLACE(package_path, '', '') + '' 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 Packagefrom splitted_packagesThis is the final result of the previous command:

Advertisement

Answer