Post reply

'merging' values of 2 colums of which is xml

  • August 12, 2011 at 1:14 am

    #242129

    Hello,

    I have a SQL 2005 database called OperationsManager (yes its from SCOM 2007) which has a view called AlertView. This view has the colums AlertStringDescription and AlertParameters.

    An example of a value for AlertStringDescription is:

    'The disk {0} on computer {1} is running out of disk space. The values that exceeded the threshold are {2}% free space and {3} free Mbytes.'

    The related value of AlertParams is:

    '<AlertParameters><AlertParameter1>O:</AlertParameter1><AlertParameter2>SLP-718.pggm-intra.intern</AlertParameter2><AlertParameter3>6</AlertParameter3><AlertParameter4>6084</AlertParameter4></AlertParameters>'

    A string value from AlertStringDescription contains one or more '{i}' (with i an integer) 'parameters'.

    I want to write a query over AlertView which not displays the values from AlertStringDescription and AlertParams as seperate values but 'merges' them into a single valu.

    Exmaple:

    'The disk O: on computer SLP-718.pggm-intra.intern is running out of disk space. The values that exceeded the threshold are 3% free space and 6084 free Mbytes.

    How can i do this?

    Best regards,

    Coen van Dijk

  • August 17, 2011 at 8:14 am

    #1369618

    Hi Coen,

    You can try this...

    DECLARE @AlertXML XML

    SELECT @AlertXML = '<AlertParameters><AlertParameter1>O:</AlertParameter1><AlertParameter2>SLP-718.pggm-intra.intern</AlertParameter2><AlertParameter3>6</AlertParameter3><AlertParameter4>6084</AlertParameter4></AlertParameters><AlertParameters><AlertParameter1>T:</AlertParameter1><AlertParameter2>SLP-724.pggm-intra.intern</AlertParameter2><AlertParameter3>3</AlertParameter3><AlertParameter4>1098</AlertParameter4></AlertParameters>'

    SELECT 'The disk ' + c2.value('AlertParameter1[1]', 'varchar(30)') + ' on computer '

    + c2.value('AlertParameter2[1]', 'varchar(30)') + ' is running out of disk space. The values that exceeded the threshold are '

    + c2.value('AlertParameter3[1]', 'varchar(30)') + '% free space and '

    + c2.value('AlertParameter4[1]', 'varchar(30)') + ' free Mbytes.'

    FROM @AlertXML.nodes('/') T(c)

    CROSS apply c.nodes ('/AlertParameters') T2(c2)

    It may not solve the entire issue, as I'm not sure of the structure of the AlertStringDescription table, however it's a start.

    Good luck!

  • August 19, 2011 at 7:10 am

    #1370697

    Hi,

    Thanks for the idea. I wil have a look at it.

    Problem in general is that the number of AlertParameters is not fixed. So it can be 1, 2, 3, ... The qury needs to be able to support this.

    Unfortunatelly in xml things are name like AlertParameter1[1], AltertParameter2[1], ... Thus the digit before [ should be a dynamic element of the query

    regards,

    Coen

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

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