none
Pulling XML from SQL Database

    Question

  • SELECT

          SDMPackageDigest.value('(/DesiredConfigurationDigest/SoftwareUpdateBundle/Annotation/DisplayName/@text)[1]', 'nvarchar(max)')  as 'SDMPackageDigest1'

    FROM  vSMS_SoftwareUpdate

     

    I'm trying to get the name out of the xml from column SDMPackageDigest and it returns null everytime. I'm trying get the custom reports for my software updates but would like the names for the report. This is my first road block. I've never pulled an XML attribute from sql before.

    <DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">

      <SoftwareUpdateBundle AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">

        <Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">

          <DisplayName Text="Security Update for Windows 7 Beta (KB958690)" />

          <Description Text="A security issue has been identified that could allow an unauthenticated remote attacker to compromise your system and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this update, you may have to restart your system. This update is provided to you and licensed under the Windows 7 Prerelease License Terms." />

        </Annotation>

        <ConfigurationMetadata SmsUniqueIdentity="fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">

          <Provider SourceType="default">

            <Operation Name="Detect">

              <Parameter Name="ScanTool">

                <Property Name="ScanToolId" Value="{0C7F160B-6AB7-4184-935B-09DFCBF1BF4C}" />

                <Property Name="MinCatalogVersion" Value="1" />

              </Parameter>

            </Operation>

            <Operation Name="Install">

              <Parameter Name="CommandLine">

                <Property Name="CommandLine" Value="/WUSInstaller /UpdateID:fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" />

              </Parameter>

              <Parameter Name="RequiresExclusiveHandling">

                <Property Name="RequiresExclusiveHandling" Value="False" />

              </Parameter>

            </Operation>

          </Provider>

        </ConfigurationMetadata>

        <Updates>

          <SoftwareUpdateReference AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_94797c79-3b60-48a7-888a-9bdf7e983f99" />

        </Updates>

      </SoftwareUpdateBundle>

    </DesiredConfigurationDigest>

    Monday, August 19, 2013 9:17 PM

Answers

  • I would use CROSS APPLY:

    DECLARE @Sample TABLE ( Data XML );
    
    INSERT INTO @Sample 
    VALUES ( N'
    <DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
    	<SoftwareUpdateBundle AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    		<Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
    			<DisplayName Text="Security Update for Windows 7 Beta (KB958690)"/>
    			<Description Text="A security issue has been identified that could allow an unauthenticated remote attacker to compromise your system and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this update, you may have to restart your system. This update is provided to you and licensed under the Windows 7 Prerelease License Terms."/>
    		</Annotation>
    		<ConfigurationMetadata SmsUniqueIdentity="fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    			<Provider SourceType="default">
    				<Operation Name="Detect">
    					<Parameter Name="ScanTool">
    						<Property Name="ScanToolId" Value="{0C7F160B-6AB7-4184-935B-09DFCBF1BF4C}"/>
    						<Property Name="MinCatalogVersion" Value="1"/>
    					</Parameter>
    				</Operation>
    				<Operation Name="Install">
    					<Parameter Name="CommandLine">
    						<Property Name="CommandLine" Value="/WUSInstaller /UpdateID:fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d"/>
    					</Parameter>
    					<Parameter Name="RequiresExclusiveHandling">
    						<Property Name="RequiresExclusiveHandling" Value="False"/>
    					</Parameter>
    				</Operation>
    			</Provider>
    		</ConfigurationMetadata>
    		<Updates>
    			<SoftwareUpdateReference AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_94797c79-3b60-48a7-888a-9bdf7e983f99"/>
    		</Updates>
    	</SoftwareUpdateBundle>
    </DesiredConfigurationDigest>
    ' );
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
            n.value('@Text', 'NVARCHAR(255)')
    FROM    @Sample
            CROSS APPLY Data.nodes('/dc:DesiredConfigurationDigest/dc:SoftwareUpdateBundle/r:Annotation/r:DisplayName') T ( n );
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
            n.value('/dc:DesiredConfigurationDigest[1]/dc:SoftwareUpdateBundle[1]/r:Annotation[1]/r:DisplayName[1]/@Text', 'NVARCHAR(255)')
    FROM    @Sample
            CROSS APPLY Data.nodes('/') T ( n );

    • Marked as answer by Matt Novitsch Monday, August 26, 2013 6:04 PM
    Tuesday, August 20, 2013 3:16 PM

All replies

  • I haven't used SDMPackageDigest, but you need to specify the default namespace. You need to make yourself familiar with the appropriate XPath queries:

    DECLARE @Xml XML = N'
    <DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
    	<SoftwareUpdateBundle AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    		<Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
    			<DisplayName Text="Security Update for Windows 7 Beta (KB958690)"/>
    			<Description Text="A security issue has been identified that could allow an unauthenticated remote attacker to compromise your system and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this update, you may have to restart your system. This update is provided to you and licensed under the Windows 7 Prerelease License Terms."/>
    		</Annotation>
    		<ConfigurationMetadata SmsUniqueIdentity="fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    			<Provider SourceType="default">
    				<Operation Name="Detect">
    					<Parameter Name="ScanTool">
    						<Property Name="ScanToolId" Value="{0C7F160B-6AB7-4184-935B-09DFCBF1BF4C}"/>
    						<Property Name="MinCatalogVersion" Value="1"/>
    					</Parameter>
    				</Operation>
    				<Operation Name="Install">
    					<Parameter Name="CommandLine">
    						<Property Name="CommandLine" Value="/WUSInstaller /UpdateID:fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d"/>
    					</Parameter>
    					<Parameter Name="RequiresExclusiveHandling">
    						<Property Name="RequiresExclusiveHandling" Value="False"/>
    					</Parameter>
    				</Operation>
    			</Provider>
    		</ConfigurationMetadata>
    		<Updates>
    			<SoftwareUpdateReference AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_94797c79-3b60-48a7-888a-9bdf7e983f99"/>
    		</Updates>
    	</SoftwareUpdateBundle>
    </DesiredConfigurationDigest>
    ';
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
    		n.value('@Text', 'NVARCHAR(255)')
    FROM    @Xml.nodes('/dc:DesiredConfigurationDigest/dc:SoftwareUpdateBundle/r:Annotation/r:DisplayName') T ( n );
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
    		n.value('/dc:DesiredConfigurationDigest[1]/dc:SoftwareUpdateBundle[1]/r:Annotation[1]/r:DisplayName[1]/@Text', 'NVARCHAR(255)')
    FROM    @Xml.nodes('/') T ( n );

    Tuesday, August 20, 2013 7:04 AM
  • I think I forgot something, its in column in a sql table already. Will the declare XML piece work for that? I could be pulling up to 500 columns each query.
    Tuesday, August 20, 2013 1:20 PM
  • I would use CROSS APPLY:

    DECLARE @Sample TABLE ( Data XML );
    
    INSERT INTO @Sample 
    VALUES ( N'
    <DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
    	<SoftwareUpdateBundle AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    		<Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
    			<DisplayName Text="Security Update for Windows 7 Beta (KB958690)"/>
    			<Description Text="A security issue has been identified that could allow an unauthenticated remote attacker to compromise your system and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this update, you may have to restart your system. This update is provided to you and licensed under the Windows 7 Prerelease License Terms."/>
    		</Annotation>
    		<ConfigurationMetadata SmsUniqueIdentity="fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d" Version="100">
    			<Provider SourceType="default">
    				<Operation Name="Detect">
    					<Parameter Name="ScanTool">
    						<Property Name="ScanToolId" Value="{0C7F160B-6AB7-4184-935B-09DFCBF1BF4C}"/>
    						<Property Name="MinCatalogVersion" Value="1"/>
    					</Parameter>
    				</Operation>
    				<Operation Name="Install">
    					<Parameter Name="CommandLine">
    						<Property Name="CommandLine" Value="/WUSInstaller /UpdateID:fd4edc28-9cdd-4f8f-a5ec-b806b1d7dd7d"/>
    					</Parameter>
    					<Parameter Name="RequiresExclusiveHandling">
    						<Property Name="RequiresExclusiveHandling" Value="False"/>
    					</Parameter>
    				</Operation>
    			</Provider>
    		</ConfigurationMetadata>
    		<Updates>
    			<SoftwareUpdateReference AuthoringScopeId="Site_0C7F160B-6AB7-4184-935B-09DFCBF1BF4C" LogicalName="SUM_94797c79-3b60-48a7-888a-9bdf7e983f99"/>
    		</Updates>
    	</SoftwareUpdateBundle>
    </DesiredConfigurationDigest>
    ' );
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
            n.value('@Text', 'NVARCHAR(255)')
    FROM    @Sample
            CROSS APPLY Data.nodes('/dc:DesiredConfigurationDigest/dc:SoftwareUpdateBundle/r:Annotation/r:DisplayName') T ( n );
    
    WITH XMLNAMESPACES ( 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    	'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    	)
    SELECT  n.query('.') ,
            n.value('/dc:DesiredConfigurationDigest[1]/dc:SoftwareUpdateBundle[1]/r:Annotation[1]/r:DisplayName[1]/@Text', 'NVARCHAR(255)')
    FROM    @Sample
            CROSS APPLY Data.nodes('/') T ( n );

    • Marked as answer by Matt Novitsch Monday, August 26, 2013 6:04 PM
    Tuesday, August 20, 2013 3:16 PM
  • I was able to get what i need from the query below. Thanks for the help.

    WITH XMLNAMESPACES ( 
    'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration' AS dc, 
    'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' AS r 
    )
    SELECT 
    SDMPackageDigest.value('/dc:DesiredConfigurationDigest[1]/dc:SoftwareUpdateBundle[1]/r:Annotation[1]/r:DisplayName[1]/@Text', 'NVARCHAR(255)') as 'MS_Patch'
    FROM vSMS_SoftwareUpdate

    Monday, August 26, 2013 6:04 PM