Requête SQL pour analyser le champ XML et récupérer des valeurs

J'essaie d'analyser un champ XML qui fait partie de la SCOM 2007 de l'Entrepôt de Données de la base de données et nous avons trouvé de nombreux exemples qui montrent les mêmes scénarios de réalisation de cette mais rien ne s'affiche pour les valeurs de retour, généralement quand je lance la requête qu'il erreurs.

Voici un exemple, une ligne de plus d'un millier j'ai besoin d'exécuter cette contre, là où j'ai envie de tirer dans les valeurs de l'Angleterre et du royaume-UNI (le GUID qui comprennent les tag sont cohérents pour chaque type de données, afin de ne jamais changer et peut être utilisé pour interroger etc). Bien sûr, chaque ligne a un autre de la ville et le code du pays et c'est ce que je veux capturer.

Le nom de la table dbo.ManagedEntityProperty et le nom de la colonne est PropertyXML

<Root>
            <Property Guid="AFB4F9E6-BF48-1737-76AD-C9B3EC325B97">192.168.1.0</Property>
            <Property Guid="5C324096-D928-76DB-E9E7-E629DCC261B1">WASPDC01.LIV10.Local</Property>
            <Property Guid="96981E2D-DECF-7CB7-DEC5-5C52046B68A6">192.168.1.0</Property>
            <Property Guid="FA3887C3-F274-306A-867C-37105A190F78">England</Property>
            <Property Guid="61AA7309-595F-576E-337E-E9335E5CA773">255.255.255.0</Property>
            <Property Guid="F8ABF27F-A169-6FCD-1862-C06F1DB4BF24">UK</Property>
            <Property Guid="B832B2DE-A649-60A1-AC13-06F1EC601E5F">Active</Property>
</Root>

Des conseils ou des conseils? J'ai tenté de mettre en œuvre des conseils comme par De requête SQL Server, xml attribut de la valeur d'un élément mais je suis encore très bien dans ma requête SQL XML de l'enfance et n'ont jamais eu à faire quelque chose comme ça avant.

OriginalL'auteur user2215800 | 2013-03-27

  1. 3

    Essayer quelque chose comme cela:

    -- declare your two GUIDs that you're interested in
DECLARE @GuidCountry UNIQUEIDENTIFIER
SET @GuidCountry = 'FA3887C3-F274-306A-867C-37105A190F78'

DECLARE @GuidCountryCode UNIQUEIDENTIFIER 
SET @GuidCountryCode = 'F8ABF27F-A169-6FCD-1862-C06F1DB4BF24'

;WITH ListOfAllProperties AS
(
    SELECT 
        ID,   -- or whatever uniquely identifies a single row in your table
        PropertyGuid = XProp.value('@Guid', 'uniqueidentifier'),
        PropertyValue = XProp.value('(.)', 'varchar(100)')
    FROM 
        dbo.ManagedEntityProperty
    CROSS APPLY
        PropertyXML.nodes('/Root/Property') AS XTbl(XProp)
)
SELECT *
FROM ListOfAllProperties
WHERE PropertyGuid IN (@GuidCountry, @GuidCountryCode)

    Fondamentalement énumère tous les <Property> des noeuds dans votre tableau en lignes individuelles, et quand empoigne les deux articles qui vous intéressent à partir de ces lignes

    Mise à jour: si vous avez besoin de montrer à ces deux valeurs comme des colonnes séparées, vous devez utiliser quelque chose comme ceci:

    SELECT 
    ID,
    CountryCode = PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidCountryCode")]/text())[1]', 'varchar(100)'),
    CountryName = PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidCountry")]/text())[1]', 'varchar(100)')
FROM 
    dbo.ManagedEntityProperty
    Génial, vraiment de réponse rapide, et ça fonctionne très bien. N'avait qu'à peaufiner les déclarer une partie comme je suis à l'aide de SQL 2005, mais c'est de ma faute parce que je n'ai pas à vous dire la version 🙂
    Si je voulais afficher la sortie, les deux valeurs XML dans la même ligne, mais les différentes colonnes, comment pourrais-je aller à ce sujet? Je suis en train d'essayer d'obtenir ma tête autour de l '(.)' et comment je pourrais modifier le script de comportement à partir de ce que, peut-être cibler chaque GUID séparément au lieu d'énumérer tous le filtrage. Sais ce que je veux dire?
    mise à jour de ma réponse

    OriginalL'auteur marc_s