Início / dba / Perguntas / 133737
Accepted
TheDemonLord
Asked: 2016-03-30 15:36:42 +0800 CST

Ajuda com SQL Server e colunas XML

  • 772

Estou escrevendo uma consulta para executar no banco de dados SCOM 2012 OperationsManager. A instância SQL é Microsoft SQL Server 2012 - 11.0.5058.0 (X64).

Dentro do banco de dados há uma tabela chamada Monitors. O schema do DB pode ser encontrado aqui: Link to Schema

Posso executar a seguinte consulta:

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

e ele retorna isso:

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>

(então o Monitor GUID e XML na colunaconfigurationxml

Posso então executar esta consulta:

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)

que fragmenta (não tenho certeza se esse é o termo certo) o XML e me permite recuperar valores de elementos específicos nessa coluna:

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

O problema que tenho é que gostaria de poder extrair valores de elementos específicos vinculados ao GUID nessa linha específica para que eu possa ter o GUID e os valores de elementos XML específicos que desejo, algo que ficaria assim:

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

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

O principal problema que estou encontrando é que, quando destruo o XML, ele perde sua referência à linha de onde veio.

Como posso vincular o conteúdo XML à linha de onde ele vem?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Tente isto:

    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

    Observe que mudei algumas coisas sobre seu código: 1) sempre use o text()acessador para obter melhor desempenho em relação a XML não digitado e 2) especifiquei tipos de dados melhores.

    Deixe-me saber se isso funciona para você.

    • 4

relate perguntas