Get List of SQL Server Agent Job Names, Category Names

Need to get a quick list of all SQL Agent jobs, with their respective categories?  Join the sysjobs table to the syscategories table:

SELECT j.[job_id], j.[name] [job_name], j.[category_id], c.name [category_name]
FROM [msdb].[dbo].[sysjobs] j
JOIN [msdb].[dbo].[syscategories] c
ON j.category_id = c.category_id
ORDER BY 2

XQuery Lab 1

XQuery is a database programming language developed for filtering and manipulating XML BLOB data.  It can be especially useful for ad-hoc reporting.  In this post, I want to show a few examples of some very basic XQuery expressions against untyped XML data.

For our examples, we’ll need sample data.  Let’s create a simple table to hold XML data:

CREATE TABLE Avengers (ID INT IDENTITY, AdditionalInfo XML) 
GO

Now let’s populate the table with a few records:

INSERT INTO Avengers VALUES('
 <Person>
 <Names>
 <Name>
 <First>Tony</First>
 <Last>Stark</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Iron Man</Alias>
 <Alias type="Other">Shellhead</Alias>
 <Alias type="Other">Golden Avenger</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person>
 <Names>
 <Name>
 <First>Bruce</First>
 <Last>Banner</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Hulk</Alias>
 <Alias type="Other">Joe Fixit</Alias>
 <Alias type="Other">Green King</Alias>
 <Alias type="Other">Green Goliath</Alias>
 <Alias type="Other">Holku</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person> 
 <Names> 
 <Name> 
 <First>Thor</First>
 <Last>Odinson</Last> 
 </Name>
 </Names> 
 <Aliases>
 <Alias type="Primary">Thor</Alias>
 <Alias type="Other">Donald Blake</Alias>
 <Alias type="Other">Sigurd Jarlson</Alias>
 <Alias type="Other">Jake Olsen</Alias>
 </Aliases>
 </Person> 
 ')
GO

Upon running a SELECT * FROM Avengers, we see the contents of the new table:

You can see that we have 3 records of XML BLOBs.  To perform a simple query, let’s pull the primary Alias for each record:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]') 
AS PrimaryAlias 
FROM Avengers
GO

We’ve used the query() XQuery method to do this – it gives us what we want, but we could make this more meaningful by associating the Person’s Name with the Alias, and also remove the XML tags:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]/text()')  
AS PrimaryAlias, 
AdditionalInfo.value('(/Person/Names/Name/Last)[1]','VARCHAR(25)') 
AS LastName 
FROM Avengers
GO

This time we’ve added the use of the text() method to remove the XML tags from the PrimaryAlias results, and introduced the value() method to obtain the LastName values – we don’t require the query method for LastName.  The value() method requires a second paramter – data type.  We used VARCHAR, and that effectively converted the result’s XML type to VARCHAR.  We could also convert the PrimaryAlias result values to VARCHAR by wrapping the query in a CONVERT function call.

More XQuery to come!

[XQuery Lab 2]