Help with SQL Server and XML columns

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:

Select 
    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
    from
     (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:

SELECT
    x.MonitorId,
    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
FROM 
    (
    SELECT 
        MonitorId, 
        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.