November 1, 2011 at 3:42 pm
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]
November 1, 2011 at 4:22 pm
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