none
SRS XML reporting question RRS feed

  • Question

  • I am attempting to report on a sql table that has a xml data type, I want to be able to report on all the nodes within the Operands node, called "RuleExpression" the sql below only gives me the first instance "Windows/All_x64_Windows_7_Client". Does anyone know how to query them both and return them as comma delimated?

    ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
     
    SELECT   app.DisplayName [Application Name],
       app.Manufacturer [Application Manufacturer],
       dt.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Description)[1]','nvarchar(MAX)')[Description],
       dt.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/OperatingSystemExpression/Operands/RuleExpression/@RuleId)[1]','nvarchar(MAX)')[RuleDT]
      
              
    FROM         dbo.fn_ListDeploymentTypeCIs(1033) AS dt INNER JOIN
                           dbo.fn_ListLatestApplicationCIs(1033) AS app ON dt.AppModelName = app.ModelName

    WHERE     (dt.IsLatest = 1) and (app.DisplayName like @Appsearchstring or dt.DisplayName like @Dtsearchstring)
     Order by app.DisplayName, dt.PriorityInLatestApp


    <?xml version="1.0"?>
    -<AppMgmtDigest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"> -<p1:DeploymentType Version="2" LogicalName="DeploymentType_e6b41230-d94c-4b72-861e-76fb1216f479" AuthoringScopeId="ScopeId_5F035480-7D2A-47C6-912C-963CBDD2332F" xmlns:p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"> <p1:Title ResourceId="Res_462755002">Microsoft Visual C++ 2005 Redistributable (X64) 8.00.004</p1:Title> <p1:Description ResourceId="Res_1675524319"/> -<p1:Requirements> -<Rule xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules" NonCompliantWhenSettingIsNotFound="false" Severity="None" id="Rule_950703b6-54eb-4d95-9a28-1097647ec192"> -<Annotation> <DisplayName Text="Operating system One of {All Windows 7 (64-bit), All Windows Server 2008 R2 (64-bit)}"/> <Description Text=""/> </Annotation> -<OperatingSystemExpression> <Operator>OneOf</Operator> -<Operands> <RuleExpression RuleId="Windows/All_x64_Windows_7_Client"/> <RuleExpression RuleId="Windows/All_x64_Windows_Server_2008_R2"/> </Operands> </OperatingSystemExpression> </Rule> </p1:Requirements> <p1:DeploymentTechnology>GLOBAL/MSIDeploymentTechnology</p1:DeploymentTechnology> <p1:Technology>MSI</p1:Technology> <p1:Hosting>Native</p1:Hosting> -<p1:Installer Technology="MSI"> -<p1:Contents> +<p1:Content Version="1" ContentId="Content_8fd7f34d-3a0e-4b39-823d-8fce71530ad3"> </p1:Contents> -<p1:DetectAction> <p1:Provider>MSI</p1:Provider> +<p1:Args> </p1:DetectAction> -<p1:InstallAction> <p1:Provider>MSI</p1:Provider> +<p1:Args>---- -<p1:Contents> <p1:Content Version="1" ContentId="Content_8fd7f34d-3a0e-4b39-823d-8fce71530ad3"/> </p1:Contents> </p1:InstallAction> -<p1:UninstallAction> <p1:Provider>MSI</p1:Provider> +<p1:Args>---- -<p1:Contents> <p1:Content Version="1" ContentId="Content_8fd7f34d-3a0e-4b39-823d-8fce71530ad3"/> </p1:Contents> </p1:UninstallAction> -<p1:CustomData> <p1:DetectionMethod>ProductCode</p1:DetectionMethod> <p1:ProductCode>{071c9b48-7c32-4621-a0ac-3f809523288f}</p1:ProductCode> <p1:ProductVersion>8.0.56336</p1:ProductVersion> <p1:InstallCommandLine>msiexec /i "vcredist.msi" /qb-</p1:InstallCommandLine> <p1:InstallContent Version="1" ContentId="Content_8fd7f34d-3a0e-4b39-823d-8fce71530ad3"/> <p1:UninstallCommandLine>msiexec /x {071c9b48-7c32-4621-a0ac-3f809523288f} /q</p1:UninstallCommandLine> -<p1:ExitCodes> <p1:ExitCode Class="Success" Code="0"/> <p1:ExitCode Class="Success" Code="1707"/> <p1:ExitCode Class="SoftReboot" Code="3010"/> <p1:ExitCode Class="HardReboot" Code="1641"/> <p1:ExitCode Class="FastRetry" Code="1618"/> </p1:ExitCodes> </p1:CustomData> </p1:Installer> </p1:DeploymentType> </AppMgmtDigest>

    Friday, November 30, 2012 4:19 PM

All replies

  • Can you please remove the expansion '+' and '-' characters from your XML and repost it - it is not properly formed.

    Josh Ash

    Monday, December 3, 2012 1:33 AM
  • Hi BBowron,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Thursday, December 6, 2012 3:07 AM
    Moderator
  • Hi,

    The value() method can only return one value, first you would need to use the nodes() methode.
         nodes() Method (xml Data Type)
         http://technet.microsoft.com/en-us/library/ms188282.aspx
    “The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.
    You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. Note that the value() method, when applied to the XML instance, returns only one value”


    This blog of Simon Ince should give a good example:
         Flattening XML Data in SQL Server
         http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thanks,
    Orsi

    Microsoft Online Community Support

    Thursday, January 24, 2013 6:52 PM
    Answerer