Using two conditions on XML attributes in the WHERE clause

Enric 81 Reputation points
2023-05-08T09:22:08.3766667+00:00

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,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2023-05-08T10:34:04.7+00:00

    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);
    
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful