RE: [mssms] SQL XQuery Help

[Stephen Leuthold]

Howdy Todd... It's "Leuthold" !
 
I used this example I found here: 
http://gallery.technet.microsoft.com/scriptcenter/Count-of-EP-Definition-59c06ea2
 and added the install and uninstall columns.
 
SELECT [CI_ID] 
      ,[CI_UniqueID] 
      ,[SDMPackageDigest] 
      ,SDMPackageDigest.value('declare namespace 
p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";;
 
      (p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)') AS 
DTTitle 
      ,SDMPackageDigest.value('declare namespace 
p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";;
 
      (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]', 
'nvarchar(max)') AS DTTechnology 
      ,SDMPackageDigest.value('declare namespace 
p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";;
 
      
(p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]',
 'nvarchar(max)') AS DTContentLocation       
      ,SDMPackageDigest.value('declare namespace 
p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";;
 
      
(p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]',
 'nvarchar(MAX)') AS Install
        ,SDMPackageDigest.value('declare namespace 
p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";;
 
      
(p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]',
 'nvarchar(MAX)') AS Uninstall
  FROM v_ConfigurationItems
  WHERE CIType_ID = 21 
 
Date: Wed, 22 May 2013 10:46:48 -0500
Subject: [mssms] SQL XQuery Help
From: hems...@gmail.com
To: mssms@lists.myitforum.com

I am trying to figure out how to write an XQuery to get the install and 
uninstall command out of the SDMDigest for a Deployment and not having much 
luck. Luethold wrote the first example of getting an apps user and contact, but 
for the life of me I am unable to recreate that in the DepploymentType SDM
XQuery Reference http://msdn.microsoft.com/en-us/library/ms189075.aspx  Here is 
the example Luethold wrote and I am trying to convert to get the values shown 
below
 SDMPackageDigest.value('(/AppMgmtDigest/Application/Owners/User/@Id)[1]', 
'nvarchar(MAX)')  [Owner],

Here is a snippet of the XML from the SDM. <AppMgmtDigest 
xmlns="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>


  <p1:DeploymentType 
xmlns:p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";
 AuthoringScopeId="ScopeId_62BA06EA-054D-4736-9FA5-F4734D1FBA6A" 
LogicalName="DeploymentType_31a6ee3b-acf6-41b8-9ea9-7c964c9091b0" Version="3">


    <p1:Installer Technology="Script">


        <p1:InstallAction>


        <p1:Provider>Script</p1:Provider>


        <p1:Args>


          <p1:Arg Name="InstallCommandLine" Type="String">FindMe.exe</p1:Arg>


        </p1:Args>


      </p1:InstallAction>


      <p1:UninstallAction>


        <p1:Provider>Script</p1:Provider>


        <p1:Args>


          <p1:Arg Name="InstallCommandLine" Type="String">FindMe.exe</p1:Arg>


        </p1:Args>


      </p1:UninstallAction>


    </p1:Installer>


  </p1:DeploymentType>


</AppMgmtDigest>