How to query Configuration Manager 2012 XML Data with SQL - XQuery
Sorry, but I had to divert from my Application Model review to discuss XML data in the CM 2012 database. At some point you will start to look at the data and want to generate some custom reports. Perhaps it’s through Report Builder or some killer application / utility that you are working on. I thought I share some of my field experiences to save you time and frustration.
Some views/tables in CM2012 will store data as XML. You can find out the data type by using the sp_help function. For example:
You can see that SDMPackageDigest is a data type of XML. I wonder what’s in there? HMM.. let’s go look.
Looks familiar? My XML Notepad test package.
From there you can generate any type of report necessary using Report Builder. This sample reports (shout out thanks to Lou Smathers for the report) shows all Application Deployment Type versions along with the Application Version. This was pulled with the following SQL Statement.
;WITH XMLNAMESPACES ( DEFAULT 'https://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest')
select App.DisplayName, App.SDMPackageDigest.value('(/AppMgmtDigest/Application/DeploymentTypes/DeploymentType/@Version)', 'nvarchar(10)') [Version]
from fn_ListApplicationCIs(1033) App
where sdmpackageversion = (SELECT MAX (sdmpackageversion)
WHERE app.modelid = App2.modelid)
order by DisplayName
Now for the IMPORTANT PAY ATTENTION information.
1. All Syntax is CASE and Whitespace Sensitive. Review Here.
This will not work and return NULL
Case must Match. Title
Here is the correct SQL
;WITH XMLNAMESPACES ( DEFAULT 'https://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' )
select SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Title)', 'nvarchar(MAX)') [Title]
where CIType_ID = 21
With xQuery and following the above syntax you can retrieve the data stored in the xml. For attributes you need to change like as follows:
select SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Title/@ResourceId)', 'nvarchar(MAX)') [ResourceID]
Here is an example where I pulled information for Applications from the v_configurationItems, XML Column, SDMPackageDigest. Note that you can use the Where and AND with the xQuery as well.
xQuery is so much fun! I hope this post has been useful. Now back to the Application Model in Configuration Manager 2012.
For those of you are curious, this is the first sql query I used to extract data.
Yes, my SQL Server did call me up and cry just a little bit then asked me to look at xQuery.