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

Enric 81 Reputation points
2023-05-08T08:07:52.16+00:00

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,

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,117 questions
0 comments No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. Enric 81 Reputation points
    2023-05-08T08:36:57.18+00:00

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

    0 comments No comments

  2. Enric 81 Reputation points
    2023-05-08T08:41:30.6466667+00:00

    It works smoothly, thanks a lot

    0 comments No comments