Post reply

Extract XML values stored in colums of table

  • September 7, 2018 at 3:56 am

    #376928

    Hi

    I have  at Table named "triggers" with a column named "body" that contain XML data that I need to extract .. My initial SQL script where I need to include values from the XML column is this:

    SELECT dbo.folders.name AS "Afdeling", dbo.job_info.name AS "Process Navn", dbo.triggers.body
    FROM dbo.triggers
    INNER JOIN dbo.job_info ON dbo.triggers.job_id = dbo.job_info.id
    INNER JOIN dbo.folders ON dbo.folders.id = dbo.job_info.folder_id
    WHERE enabled = 1 AND body LIKE '%EmailMonitorTrigger%'
    GROUP BY dbo.folders.name,dbo.job_info.name,dbo.triggers.body
    ORDER BY dbo.folders.name,dbo.job_info.name,dbo.triggers.body;

    The XML content of the body table is this:

    <EmailMonitorTrigger Version="2">  <Interval>00:02:00</Interval>  <ImapServer>outlook.office365.com</ImapServer>  <ServerPort>993</ServerPort>  <EnableSSL>true</EnableSSL>  <UserName>RPA_ADM_01@i-r.dk</UserName>  <Password>9a297a5c5beb9ca39883118a2f1943fad4b04a7d833a6624</Password>  <MailFolder>"INBOX"</MailFolder>  <ProcessUnreadOnly>true</ProcessUnreadOnly>  <FromContains />  <ToContains />  <SubjectContains>LASERNET INVOICE FAIL LIST #</SubjectContains>  <BodyContains /> </EmailMonitorTrigger>

    I need to include the value of <Interval></Interval> and <SubjectContains></SubjectContains> into my above query.

    Can anyone please help me 🙂

    Best Regards
    Stig 🙂

  • September 10, 2018 at 4:40 am

    #2005020

    I found a solution using another forum, and the solution if anyone is interested was this :

    SELECT Afdeling,[Process Navn],body,Interval,SubjectContains
    FROM
    (
    SELECT DISTINCT dbo.folders.name AS "Afdeling",
    dbo.job_info.name AS "Process Navn",
    CAST(t.body AS nvarchar(max)) AS body,
    n.value('Interval[1]','time') AS Interval,
    n.value('SubjectContains[1]','varchar(100)') AS SubjectContains
    FROM (
    SELECT job_id,CAST(body AS xml) AS body
    FROM dbo.triggers
    WHERE enabled = 1 AND body LIKE '%EmailMonitorTrigger%'
    ) t
    OUTER APPLY body.nodes('/EmailMonitorTrigger')m(n)
    INNER JOIN dbo.job_info ON t.job_id = dbo.job_info.id
    INNER JOIN dbo.folders ON dbo.folders.id = dbo.job_info.folder_id
    )t
    ORDER BY Afdeling,[Process Navn],body,Interval,SubjectContains

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

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