CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
C. M.
C. M.

Reputation: 41

Get value of XML column in SQL

I try to access the DisplayName - text of the XML below via SQL. Using - SDMPackageDigest.value('(/DesiredConfigurationDigest/SoftwareUpdateBundle/Annotation/DisplayName/@Text)[1]', 'varchar(MAX)') as 'Display Name' - just returns a NULL value. Where's the error in this?

<DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
  <SoftwareUpdateBundle AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160" LogicalName="SUM_41a6b6d6-6976-49b5-a4c4-0121dc96189c" Version="200">
    <Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
      <DisplayName Text="2019-05 Servicing Stack Update for Windows 10 Version 1903 for x86-based Systems (KB4498523)" />
      <Description Text="Install this update to resolve issues in Windows. For a complete listing of the issues that are included in this update, see the associated Microsoft Knowledge Base article for more information. After you install this item, you may have to restart your computer." />
    </Annotation>
    <ConfigurationMetadata SmsUniqueIdentity="41a6b6d6-6976-49b5-a4c4-0121dc96189c" Version="200">
      <Provider SourceType="default">
        <Operation Name="Detect">
          <Parameter Name="ScanTool">
            <Property Name="ScanToolId" Value="{54F0FA43-F0DC-403D-A5AF-4B54F58C1160}" />
            <Property Name="MinCatalogVersion" Value="1" />
          </Parameter>
        </Operation>
        <Operation Name="Install">
          <Parameter Name="CommandLine">
            <Property Name="CommandLine" Value="/WUSInstaller /UpdateID:41a6b6d6-6976-49b5-a4c4-0121dc96189c" />
          </Parameter>
          <Parameter Name="RequiresExclusiveHandling">
            <Property Name="RequiresExclusiveHandling" Value="True" />
          </Parameter>
        </Operation>
      </Provider>
    </ConfigurationMetadata>
    <SupersededUpdates>
      <SoftwareUpdateReference AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160" LogicalName="SUM_41325597-7968-4eeb-8113-6411cbbe8c90" />
    </SupersededUpdates>
    <Updates>
      <SoftwareUpdateReference AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160" LogicalName="SUM_775e479e-a5ca-4530-9c06-edfd298c824a" />
    </Updates>
  </SoftwareUpdateBundle>
</DesiredConfigurationDigest>

Upvotes: 0

Views: 175

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

The default namespace xmlns is specified twice in the XML. The solution is to keep one of them as-is, and assign an alias to the second one.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, SDMPackageDigest XML);
INSERT INTO @tbl
VALUES
(N'<DesiredConfigurationDigest xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration">
    <SoftwareUpdateBundle AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160"
                          LogicalName="SUM_41a6b6d6-6976-49b5-a4c4-0121dc96189c" Version="200">
        <Annotation xmlns="http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules">
            <DisplayName Text="2019-05 Servicing Stack Update for Windows 10 Version 1903 for x86-based Systems (KB4498523)"/>
            <Description Text="Install this update to resolve issues in Windows. For a complete listing of the issues that are included in this update, see the associated Microsoft Knowledge Base article for more information. After you install this item, you may have to restart your computer."/>
        </Annotation>
        <ConfigurationMetadata SmsUniqueIdentity="41a6b6d6-6976-49b5-a4c4-0121dc96189c"
                               Version="200">
            <Provider SourceType="default">
                <Operation Name="Detect">
                    <Parameter Name="ScanTool">
                        <Property Name="ScanToolId" Value="{54F0FA43-F0DC-403D-A5AF-4B54F58C1160}"/>
                        <Property Name="MinCatalogVersion" Value="1"/>
                    </Parameter>
                </Operation>
                <Operation Name="Install">
                    <Parameter Name="CommandLine">
                        <Property Name="CommandLine"
                                  Value="/WUSInstaller /UpdateID:41a6b6d6-6976-49b5-a4c4-0121dc96189c"/>
                    </Parameter>
                    <Parameter Name="RequiresExclusiveHandling">
                        <Property Name="RequiresExclusiveHandling" Value="True"/>
                    </Parameter>
                </Operation>
            </Provider>
        </ConfigurationMetadata>
        <SupersededUpdates>
            <SoftwareUpdateReference AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160"
                                     LogicalName="SUM_41325597-7968-4eeb-8113-6411cbbe8c90"/>
        </SupersededUpdates>
        <Updates>
            <SoftwareUpdateReference AuthoringScopeId="Site_54F0FA43-F0DC-403D-A5AF-4B54F58C1160"
                                     LogicalName="SUM_775e479e-a5ca-4530-9c06-edfd298c824a"/>
        </Updates>
    </SoftwareUpdateBundle>
</DesiredConfigurationDigest>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration', 
    'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules' as ns2)
SELECT ID
    , col.value('@Text','VARCHAR(200)') AS Result
FROM @tbl tbl
    CROSS APPLY tbl.SDMPackageDigest.nodes('/DesiredConfigurationDigest/SoftwareUpdateBundle/ns2:Annotation/ns2:DisplayName') AS tab(col)

Output

+----+----------------------------------------------------------------------------------------------+
| ID |                                            Result                                            |
+----+----------------------------------------------------------------------------------------------+
|  1 | 2019-05 Servicing Stack Update for Windows 10 Version 1903 for x86-based Systems (KB4498523) |
+----+----------------------------------------------------------------------------------------------+

Upvotes: 3

Sasan Ahmadi
Sasan Ahmadi

Reputation: 697

The problem is in your xml namespace which does not exist. If you remove the xmlns nodes from your xml it would work fine. If you plan to use an xmlns in your xml document it should be a valid one.

Upvotes: 2

Related Questions