Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
Pulling XML from SQL Database

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |