XML Query Help

  • I have a piece of XML in a table that looks like this:

    Servername XMLField

    Server1 XMLData

    The XML looks aprox like this

    <COMMAND>

    <RESULTS>

    <CIM>

    <INSTANCE>

    <PROPERTY name='name1'>

    <VALUE>value1</VALUE>

    </PROPERTY>

    <PROPERTY name='name2'>

    <VALUE>value2</VALUE>

    </PROPERTY>

    etc

    </INSTANCE>

    <INSTANCE>

    repeat from above

    </INSTANCE>

    </CIM>

    </RESULTS>

    </COMMAND>

    What I want to get back (preferably in a single query)

    ServerName Name1 Name2

    Server1 Value1 Value2

    Server2 Value1 Value2

    Name1 and Name2 are fixed values (there are about a dozen of them as seen below).

    Is this even possible or do I need to process each row one at a time? And either way can someone help me with the code, its way beyond my current xquery ability.

    Below is code to create the table and load 2 rows with a piece of the actual XML data.

    CREATE TABLE XMLHelp (ServerName varchar(100), XMLField XML)

    INSERT INTO XMLHelp VALUES ('Server1',

    '<COMMAND SEQUENCENUM="1" ISSUEDFROM="Server1" STARTTIME="11-01-2011T14:53:50" EVERYCOUNT="0">

    <RESULTS NODE="Server1">

    <CIM>

    <INSTANCE CLASSNAME="Win32_Service">

    <PROPERTY NAME="Caption" TYPE="string">

    <VALUE>Application Experience</VALUE>

    </PROPERTY>

    <PROPERTY NAME="CreationClassName" TYPE="string">

    <VALUE>Win32_Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Description" TYPE="string">

    <VALUE>Processes application compatibility cache requests for applications as they are launched</VALUE>

    </PROPERTY>

    <PROPERTY NAME="DisplayName" TYPE="string">

    <VALUE>Application Experience</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ErrorControl" TYPE="string">

    <VALUE>Normal</VALUE>

    </PROPERTY>

    <PROPERTY NAME="InstallDate" PROPAGATED="true" TYPE="datetime" />

    <PROPERTY NAME="Name" TYPE="string">

    <VALUE>AeLookupSvc</VALUE>

    </PROPERTY>

    <PROPERTY NAME="PathName" TYPE="string">

    <VALUE>C:\Windows\system32\svchost.exe -k netsvcs</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ServiceType" TYPE="string">

    <VALUE>Share Process</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Started" TYPE="boolean">

    <VALUE>TRUE</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartMode" TYPE="string">

    <VALUE>Auto</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartName" TYPE="string">

    <VALUE>localSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="State" TYPE="string">

    <VALUE>Running</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Status" TYPE="string">

    <VALUE>OK</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemCreationClassName" TYPE="string">

    <VALUE>Win32_ComputerSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemName" TYPE="string">

    <VALUE>Server1</VALUE>

    </PROPERTY>

    </INSTANCE>

    <INSTANCE CLASSNAME="Win32_Service">

    <PROPERTY NAME="Caption" TYPE="string">

    <VALUE>Application Layer Gateway Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="CreationClassName" TYPE="string">

    <VALUE>Win32_Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Description" TYPE="string">

    <VALUE>Provides support for 3rd party protocol plug-ins for Internet Connection Sharing</VALUE>

    </PROPERTY>

    <PROPERTY NAME="DisplayName" TYPE="string">

    <VALUE>Application Layer Gateway Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ErrorControl" TYPE="string">

    <VALUE>Normal</VALUE>

    </PROPERTY>

    <PROPERTY NAME="InstallDate" PROPAGATED="true" TYPE="datetime" />

    <PROPERTY NAME="Name" TYPE="string">

    <VALUE>ALG</VALUE>

    </PROPERTY>

    <PROPERTY NAME="PathName" TYPE="string">

    <VALUE>C:\Windows\System32\alg.exe</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ServiceType" TYPE="string">

    <VALUE>Own Process</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Started" TYPE="boolean">

    <VALUE>FALSE</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartMode" TYPE="string">

    <VALUE>Manual</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartName" TYPE="string">

    <VALUE>NT AUTHORITY\LocalService</VALUE>

    </PROPERTY>

    <PROPERTY NAME="State" TYPE="string">

    <VALUE>Stopped</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Status" TYPE="string">

    <VALUE>OK</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemCreationClassName" TYPE="string">

    <VALUE>Win32_ComputerSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemName" TYPE="string">

    <VALUE>Server1</VALUE>

    </PROPERTY>

    </INSTANCE>

    </CIM>

    </RESULTS>

    </COMMAND>')

    INSERT INTO XMLHelp VALUES ('Server2',

    '<COMMAND SEQUENCENUM="1" ISSUEDFROM="Server2" STARTTIME="11-01-2011T14:53:50" EVERYCOUNT="0">

    <RESULTS NODE="Server2">

    <CIM>

    <INSTANCE CLASSNAME="Win32_Service">

    <PROPERTY NAME="Caption" TYPE="string">

    <VALUE>Application Experience</VALUE>

    </PROPERTY>

    <PROPERTY NAME="CreationClassName" TYPE="string">

    <VALUE>Win32_Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Description" TYPE="string">

    <VALUE>Processes application compatibility cache requests for applications as they are launched</VALUE>

    </PROPERTY>

    <PROPERTY NAME="DisplayName" TYPE="string">

    <VALUE>Application Experience</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ErrorControl" TYPE="string">

    <VALUE>Normal</VALUE>

    </PROPERTY>

    <PROPERTY NAME="InstallDate" PROPAGATED="true" TYPE="datetime" />

    <PROPERTY NAME="Name" TYPE="string">

    <VALUE>AeLookupSvc</VALUE>

    </PROPERTY>

    <PROPERTY NAME="PathName" TYPE="string">

    <VALUE>C:\Windows\system32\svchost.exe -k netsvcs</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ServiceType" TYPE="string">

    <VALUE>Share Process</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Started" TYPE="boolean">

    <VALUE>TRUE</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartMode" TYPE="string">

    <VALUE>Auto</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartName" TYPE="string">

    <VALUE>localSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="State" TYPE="string">

    <VALUE>Running</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Status" TYPE="string">

    <VALUE>OK</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemCreationClassName" TYPE="string">

    <VALUE>Win32_ComputerSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemName" TYPE="string">

    <VALUE>Server2</VALUE>

    </PROPERTY>

    </INSTANCE>

    <INSTANCE CLASSNAME="Win32_Service">

    <PROPERTY NAME="Caption" TYPE="string">

    <VALUE>Application Layer Gateway Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="CreationClassName" TYPE="string">

    <VALUE>Win32_Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Description" TYPE="string">

    <VALUE>Provides support for 3rd party protocol plug-ins for Internet Connection Sharing</VALUE>

    </PROPERTY>

    <PROPERTY NAME="DisplayName" TYPE="string">

    <VALUE>Application Layer Gateway Service</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ErrorControl" TYPE="string">

    <VALUE>Normal</VALUE>

    </PROPERTY>

    <PROPERTY NAME="InstallDate" PROPAGATED="true" TYPE="datetime" />

    <PROPERTY NAME="Name" TYPE="string">

    <VALUE>ALG</VALUE>

    </PROPERTY>

    <PROPERTY NAME="PathName" TYPE="string">

    <VALUE>C:\Windows\System32\alg.exe</VALUE>

    </PROPERTY>

    <PROPERTY NAME="ServiceType" TYPE="string">

    <VALUE>Own Process</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Started" TYPE="boolean">

    <VALUE>FALSE</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartMode" TYPE="string">

    <VALUE>Manual</VALUE>

    </PROPERTY>

    <PROPERTY NAME="StartName" TYPE="string">

    <VALUE>NT AUTHORITY\LocalService</VALUE>

    </PROPERTY>

    <PROPERTY NAME="State" TYPE="string">

    <VALUE>Stopped</VALUE>

    </PROPERTY>

    <PROPERTY NAME="Status" TYPE="string">

    <VALUE>OK</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemCreationClassName" TYPE="string">

    <VALUE>Win32_ComputerSystem</VALUE>

    </PROPERTY>

    <PROPERTY NAME="SystemName" TYPE="string">

    <VALUE>Server2</VALUE>

    </PROPERTY>

    </INSTANCE>

    </CIM>

    </RESULTS>

    </COMMAND>')

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • There's a couple different ways to do this, here's a quick and simple approach:

    select ServerName, InstanceValues.value('(./@CLASSNAME)[1]', 'varchar(64)') AS ClassName,

    InstanceValues.value('(PROPERTY[@NAME="Caption"]/VALUE)[1]', 'varchar(64)') AS Caption,

    InstanceValues.value('(PROPERTY[@NAME="CreationClassName"]/VALUE)[1]', 'varchar(64)') AS CreationClassName,

    InstanceValues.value('(PROPERTY[@NAME="Description"]/VALUE)[1]', 'varchar(256)') AS [Description],

    InstanceValues.value('(PROPERTY[@NAME="DisplayName"]/VALUE)[1]', 'varchar(64)') AS DisplayName,

    InstanceValues.query('.')

    FROM #XMLHelp CROSS APPLY

    XMLField.nodes('COMMAND/RESULTS/CIM/INSTANCE') AS x(InstanceValues)

    Note this doesn't take care of all the columns, figure you could choose which ones you want. Just add more columns as you see fit.

    I added a final column of InstanceValues.query('.') just to show the results of the xml.nodes() method used to split the individual row XML documents into multiple nodesets per /INSTANCE element.

    -Eddie

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply