XML Whitespaces/Unwanted Characters
This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson: 

select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]','nvarchar(10)')SiteID