Using two conditions on XML attributes in the WHERE clause

Question

Dear all,

I would need to apply two conditions on the same WHERE clause using EXIST.

XML excerpt

Jobs>
  <Job JobName="Formulatrix Database Index Rebuilding.Subplan_1" JobEnabled="true" ScheduleName="Formulatrix Database Index Rebuilding.Subplan_1" ScheduleFrequency="Weekly" ScheduleInterval="Sat " ScheduleTime="01:00:00" ScheduleTimeDescription="Starting at 01:00:00" NextRunTime="2023-05-13 01:00:00" LastRunStatus="Successful" />
  <Job JobName="Integrity_check_DBCC" JobEnabled="true" ScheduleName="monthly_integrity_check_schedule_wave2" ScheduleFrequency="Every 1 Months" ScheduleInterval="First Sat" ScheduleTime="20:00:00" ScheduleTimeDescription="Starting at 20:00:00" NextRunTime="2023-06-03 20:00:00" LastRunStatus="Successful" />
  <Job JobName="syspolicy_purge_history" JobEnabled="true" ScheduleName="syspolicy_purge_history_schedule" ScheduleFrequency="Daily" ScheduleInterval="Every Day" ScheduleTime="02:00:00" ScheduleTimeDescription="Starting at 02:00:00" NextRunTime="2023-05-09 02:00:00" LastRunStatus="Successful" />
</Jobs>

This query shows all the rows which has the "Integrity_check_DBCC" value but I only want the ones with LastRunStatus equals to "Failed" not all possible status

SELECT

JOBSINFO.query('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC")]')

FROM DBO.SQL_consolidated WHERE

(jobsinfo.exist('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC")]') = 1);

Something like that but it does not work:

(jobsinfo.exist('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC"')

AND (jobsinfo.exist('/Jobs/Job[contains(@LastRunStatus,"Failed")]') = 1)

(jobsinfo.exist('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC"] AND [@LastRunStatus="Failed")]')= 1)

Thanks for your help,

Answer 1

You were close, but the full condition should be in a single pair of square brackets.

DECLARE @tbl TABLE(id int NOT NULL PRIMARY KEY,
                   xml xml)
INSERT @tbl(id, xml)
   VALUES(1, '<Jobs>
  <Job JobName="Formulatrix Database Index Rebuilding.Subplan_1" JobEnabled="true" ScheduleName="Formulatrix Database Index Rebuilding.Subplan_1" ScheduleFrequency="Weekly" ScheduleInterval="Sat " ScheduleTime="01:00:00" ScheduleTimeDescription="Starting at 01:00:00" NextRunTime="2023-05-13 01:00:00" LastRunStatus="Successful" />
  <Job JobName="Integrity_check_DBCC" JobEnabled="true" ScheduleName="monthly_integrity_check_schedule_wave2" ScheduleFrequency="Every 1 Months" ScheduleInterval="First Sat" ScheduleTime="20:00:00" ScheduleTimeDescription="Starting at 20:00:00" NextRunTime="2023-06-03 20:00:00" LastRunStatus="Successful" />
  <Job JobName="syspolicy_purge_history" JobEnabled="true" ScheduleName="syspolicy_purge_history_schedule" ScheduleFrequency="Daily" ScheduleInterval="Every Day" ScheduleTime="02:00:00" ScheduleTimeDescription="Starting at 02:00:00" NextRunTime="2023-05-09 02:00:00" LastRunStatus="Successful" />
</Jobs>')

SELECT * FROM @tbl

SELECT xml.query('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC")]')
FROM @tbl 
WHERE (xml.exist('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC") and @LastRunStatus="Failed"]') = 1);