I am writing a query to run against the SCOM 2012 OperationsManager database. SQL instance is Microsoft SQL Server 2012 - 11.0.5058.0 (X64).

Within the database is a table called Monitors. The schema of the DB can be found here: Link to Schema

I can run the following query:

select monitorid, configurationxml 
from dbo.monitor 
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'

and it returns this:

monitorid                               configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>

(so the Monitor GUID and XML in the column configurationxml

I can then run this query:

    D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
    D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
    D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
    D.C.value('CounterName[1]','varchar(4000)') Countertype
    FROM (Select n.c.query('.') as xmlquery
     (Select cast(ConfigurationXML as xml) Recxml
     FROM [dbo].[monitor] mt)
    a Cross Apply Recxml.nodes('/') N(C)) r
    Cross Apply xmlquery.nodes('/') D(C)

which shreds (not sure if that is the right term) the XML and allows me to retrieve specific element values against that Column:

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

The problem I have is that I would like to be able to pull specific element values linked to the GUID in that specific row so that I can then have the GUID and the xpecific XML element values that I want, something that would look like this:

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

4960E39A-59C8-A2C2-99B1-59B73D73156F           900                  4                            10 PercentFree

The main problem I am encountering is that when I shred the XML it loses its reference to the row it came from.

How can I link the XML content to the row it is coming from?


Try this:

    x.configXml.value('(IntervalSeconds/text())[1]','INT') CheckInterval,
    x.configXml.value('(NumSamples/text())[1]','INT') SamplesBeforeDown,
    x.configXml.value('(SystemDriveWarningThreshold/text())[1]','INT') SystemDriveWarningThreshold,
    x.configXml.value('(CounterName/text())[1]','VARCHAR(100)') Countertype
        CAST( ConfigurationXML AS XML ) AS configXml
    FROM [dbo].[monitor] mt
    WHERE MonitorId = '4960E39A-59C8-A2C2-99B1-59B73D73156F'
    ) x

Note I have changed a couple of things about your code: 1) always use the text() accessor for better performance against untyped XML and 2) I've specified better datatypes.

Let me know if that works for you.

| improve this answer | |
  • 1
    You Sir, are a scholar and a Gentleman! That sorted it! – TheDemonLord Mar 30 '16 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.