Looking values in XML attributes in a XML column by using T-SQL

Question

Dear all, I'd like to get only one recurrence but it is returning more than one, obviously the condition is bad built, can you help me on the syntaxis?

XML column (all the contents for a concrete row)

<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>

It should return (only) :

<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" />

Instead of returning the whole conttens of the jobsinfo XML column

SELECT JOBSINFO, hostname,* FROM DBO.SQL1 WHERE (jobsinfo.exist('/Jobs/Job[contains(@JobName,"Integrity_check_DBCC")]') = 1)

Thanks in advance,

Answer 1

Try this:

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

Answer 2

@Viorel , i guess you answered but some issue has the site as I don't see it

Answer 3

It works smoothly, thanks a lot