Answered by:
Pulling XML from SQL Database

-
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>
Question
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 NovitschMicrosoft employee Monday, August 26, 2013 6:04 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 );
-
-
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 NovitschMicrosoft employee Monday, August 26, 2013 6:04 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