Xml Query

  • Hi,

    I would like to send an email to a customer with deadlock information.

    I did set up an event notification to capture Deadlock information and I do write it to a table in XML format (service broker)

    Now I would like to send an email with the information of the deadlock.

    I could use a query wit a lot of unions, but I'm sure there is a far better way to do this.

    Could someone point me in the right direction?

    My current solution:

    declare @m nvarchar(max)

    set @m =

    N'<H2>Deadlock notification</H2>' +

    N'<table border = "1">' +

    N'<tr><th>LoginName</th><th>dbname</th><th>isolation</th><th>clientapp</th><th>hostname</th><th>Statement</th></tr>' +

    CAST((

    select

    td = LoginName,''

    ,td = dbname,''

    ,td = isolation,''

    ,td = clientapp,''

    ,td = hostname,''

    ,td = Statement,''

    from

    (select

    xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[1]','varchar(max)') as LoginName

    ,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[1]','varchar(max)')) as dbname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[1]','varchar(max)') as Isolation

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[1]','varchar(max)') as clientapp

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[1]','varchar(max)') as hostname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[1]','varchar(max)') as Statement

    from dbo.tbl_deadlocknotification

    union all

    select

    xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[2]','varchar(max)') as LoginName

    ,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[2]','varchar(max)')) as dbname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[2]','varchar(max)') as Isolation

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[2]','varchar(max)') as clientapp

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[2]','varchar(max)') as hostname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[2]','varchar(max)') as Statement

    from dbo.tbl_deadlocknotification

    union all

    select

    xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[3]','varchar(max)') as LoginName

    ,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[3]','varchar(max)')) as dbname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[3]','varchar(max)') as Isolation

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[3]','varchar(max)') as clientapp

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[3]','varchar(max)') as hostname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[3]','varchar(max)') as Statement

    from dbo.tbl_deadlocknotification

    union all

    select

    xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[4]','varchar(max)') as LoginName

    ,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[4]','varchar(max)')) as dbname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[4]','varchar(max)') as Isolation

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[4]','varchar(max)') as clientapp

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[4]','varchar(max)') as hostname

    ,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[4]','varchar(max)') as Statement

    from dbo.tbl_deadlocknotification) as tbl_to_format

    where LoginName <> statement

    for XML path('tr'),Type ) AS NVARCHAR(MAX) ) + N'</table>';

    exec msdb.dbo.sp_send_dbmail

    @recipients = @recipients

    ,@subject = 'Deadlock info'

    ,@body = @m

    ,@body_format = 'HTML'

    Greetings,

    StriX

  • A better way is to use the nodes method

    http://technet.microsoft.com/en-us/library/ms188282.aspx

    A good article on this: Ad-Hoc XML File Querying by Seth Delconte

    https://www.simple-talk.com/content/article.aspx?article=1756

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply