CM SQL Query extracting SDMPackageDigest info

For some objects in the console, the metadata for that object is within an xml-formatted object, saved as a single field in SQL. As XML, that makes it a bit more difficult to tease out what you might want to see, in a SQL query or SQL Report.

One of those objects you might be interested in would be "what is the metadata visible to end users, when they view it in Software Center".

Example, looking at
select * from dbo.fn_ListApplicationCIs(1033) LPC 
you'll see that one of the fields is SDMPackageDigest.

If you click on that in SQL Server Management Studio, you'll notice quite a few fields, with the data you might want to see in a report, like Title, Description, Icon.  You might also notice that there are two fields for "Title".  One is the Title that one would see in the CM Console.  The other is the Title that an end user would see in Software Center.  They do not necessarily have to be the same name. 

Teasing out what is within SDMPackageDigest uses this line in SQL:

;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1) 

That line usually needs to be at the top of your SQL, and then later in the code you'll notice that 'p1' is referenced.

For some sample sql code for pulling out some of the information from an Application, specifically what is 'seen' by end users in Software Center, when that application is deployed as available (note, limited to Available, not required and visible, just Available, for this demo), grab --> this <--

Note that the Icon is listed... but in order to tease out the actual icon so it is visible in a report, see -->this<-- for how to format the field in Report Builder.

Caveats: this isn't (cannot be) perfect.  For example, one can have multiple Categories (my sample shows 'what if' you had 2), and multiple Tags (this sample shows 3).  

Categories is what you, as an Admin, can elect to give to an application, so when someone is in Software Center, at the top, they can select the Filter, and filter to a category you defined.  This may be useful for things where you know there is kind of a "suite" for people with the Developer role in your company.

Tags is when you, as an Admin, can elect to give an application keywords; so if someone types into the "Search" window in Software Center, like say... 'browser', if you make Available Edge Beta, Edge, Chrome Beta, Chrome, and Firefox, all of those would show up in the results (If you have given all of those that Keyword)

Another example of cannot-have-everything perfectly is my example is presuming you only support 1 language, and only configure Software Center to display in one language only.  If you noticed, you 'could' elect to have specific text for each application in different languages; in which case the SDMPackageDigest gets bigger and bigger for every language added.  

What I'm saying is this is a starting point; your environment may be different.