Geek asks and answers

SQL Query to parse an XML field and get values

I am trying to parse an XML field that is part of the SCOM 2007 data warehouse database, and I have found many examples that show similar scenarios for achieving this, but nothing returns, usually when I run a query as errors.

Here is an example, one line out of a thousand or so, I need to run this against where I want to pull the values ​​of England and the UK (the GUID that contains the tag is consistent for each data type, so never change and can be used for query, etc. d.). Of course, in each line there is a different city and country code, and this is exactly what I want to capture.

The table name is dbo.ManagedEntityProperty and the column name is 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>

? xml SQL Server , SQL XML - .

+5
1

- :

-- 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)

<Property> , ,

:, , - :

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
+3

More articles:


All Articles