That helps immensely Stephen - thank you! I don't understand it but that will add to the fun. Jason From: [email protected] Subject: Re: [mssms] SQL query & XML Date: Sat, 14 Feb 2015 09:43:03 -0600 To: [email protected]
Does this help? http://blogs.technet.com/b/hhoy/archive/2012/07/19/how-to-query-configuration-manager-2012-xml-data-with-sql-xquery.aspx -Stephen On Feb 14, 2015, at 9:29 AM, Jason Wallace <[email protected]> wrote: Hi there folks I wonder if some kind soul would be able to guide me on something which I am trying to develop please. I have a SQL query: SELECT fn_AppDeploymentAssetDetails_1.MachineName, fn_AppDeploymentAssetDetails_1.CollectionName, fn_AppDeploymentAssetDetails_1.AppName, fn_AppDeploymentAssetDetails_1.DTName, fn_AppDeploymentAssetDetails_1.Technology, fn_AppDeploymentAssetDetails_1.DeploymentIntent, fn_AppDeploymentAssetDetails_1.StartTime, fn_AppDeploymentAssetDetails_1.StatusType, fn_AppDeploymentAssetDetails_1.AppStatusType, fn_AppDeploymentAssetDetails_1.ComplianceState, fn_AppDeploymentAssetDetails_1.EnforcementState, fn_AppDeploymentAssetDetails_1.DTResultID, fn_AppDeploymentAssetDetails_1.InstalledState, fn_ListDeploymentTypeCIs_1.SDMPackageDigest FROM dbo.fn_AppDeploymentAssetDetails(1033) AS fn_AppDeploymentAssetDetails_1 INNER JOIN dbo.fn_ListDeploymentTypeCIs(1033) AS fn_ListDeploymentTypeCIs_1 ON fn_AppDeploymentAssetDetails_1.DTCI = fn_ListDeploymentTypeCIs_1.CI_ID which shows all of the deployments that I have and then the systems and the deployment type that was run. The ask from the customer now is for the App-V applications to be able to also show the icons published by this deployment type and the manifest file. The idea then is to join this with discovery data so that we are able to show that the package has streamed down and when the App-V application was last run on this system. Of course this would be great were the content not represented in XML held within SQL so I wonder if someone can guide on parsing the XML to build some rows please? Thanks in advance Jason
