Because the only text that you are dealing with is alphanumeric, you can change 
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
to
FOR XML PATH('')), 1, 1,...

Regarding your first question about the DBA position.. I like try to feel them out to understand if I'm replacing someone or filling a new position and why. Often a...

No problem!

This can be improved but here's one way: 
WITH piv AS
(
SELECT
 AID,
 name1 = MAX(CASE [name] WHEN 'LN' THEN [name] END),
 name2 =...

Your double quotes don't look right they look like ASCII-148's whereas you want ASCII-34's. Try this:

SELECT
event_xml.value('(./@name)', 'varchar(1000)') as event_name,
event_xml.value('(./data[@name="database_id"]/value)[1]', 'int')...

Your first Xpath Expression for Item/ItemNumber is wrong

Instead of 
N.value('(Item/ItemNumber[1])','int')
You want 
N.value('(Item/ItemNumber)[1]','int')

declare @packXML XML = '<Pack>
<PackID>1</PackID>
...

Building off Thom's solution, you could do something like this (note that I can't test this as I don't have an SSRS DB available)

WITH getxml AS

I'm not sure about this but, for a user to see object info they would need VIEW DEFINITION rights at the DB, Schema and Object level. Does this help? 

Yes, loops bad. Set-based good. 

First for some sample data:
IF OBJECT_ID('tempdb..#stu') IS NOT NULL DROP TABLE #stu;
CREATE TABLE #stu(nid int NULL, student_name varchar(100) NOT NULL);

I'm a little late here but I have a function that is designed for exactly this type of thing. If you grab a copy on NGrams8k you could create...