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    &lt;ComputerName&gt;$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$&lt;/ComputerName&gt;&lt;DiskLabel&gt;$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$&lt;/DiskLabel&gt;&lt;CounterName&gt;PercentFree&lt;/CounterName&gt;&lt;IntervalSeconds&gt;900&lt;/IntervalSeconds&gt;&lt;NumSamples&gt;4&lt;/NumSamples&gt;&lt;SystemDriveWarningThreshold&gt;10&lt;/SystemDriveWarningThreshold&gt;&lt;SystemDriveErrorThreshold&gt;5&lt;/SystemDriveErrorThreshold&gt;&lt;NonSystemDriveWarningThreshold&gt;10&lt;/NonSystemDriveWarningThreshold&gt;&lt;NonSystemDriveErrorThreshold&gt;5&lt;/NonSystemDriveErrorThreshold&gt;


(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.