假设XML是一个变量的值。

SQL

DECLARE @xml XML = 
N'<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="9/15/2021 11:38:35 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="X" DTS:CreatorName="Y" DTS:DTSID="{5BDDA1D9-F546-47BC-81BB-E29C0E22D5DA}"
                DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="16.0.694.0" DTS:LocaleID="1033" DTS:ObjectName="Mine" DTS:PackageType="5" DTS:VersionBuild="195" DTS:VersionGUID="{68B021E9-5664-4E2B-A64D-C8B5CAF38316}">
    <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
    <DTS:ConnectionManagers>
        <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[A]" DTS:CreationName="OLEDB" DTS:DTSID="{AE299D88-1DB8-48B1-8548-31BE5C2F2721}" DTS:ObjectName="A">
            <DTS:PropertyExpression DTS:Name="InitialCatalog">@[User::A]</DTS:PropertyExpression>
            <DTS:PropertyExpression DTS:Name="ServerName">@[User::B]</DTS:PropertyExpression>
            <DTS:ObjectData>
                <DTS:ConnectionManager DTS:Retain="True" DTS:ConnectRetryCount="1" DTS:ConnectRetryInterval="5"
                                       DTS:ConnectionString="Data Source=XXX;Initial Catalog=XXX;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Application Name=XXXXX;"/>
            </DTS:ObjectData>
        </DTS:ConnectionManager>
    </DTS:ConnectionManagers>
</DTS:Executable>';

;WITH XMLNAMESPACES(DEFAULT 'www.microsoft.com/SqlServer/Dts')
SELECT result = @xml.value('(/Executable/Property/text())[1]', 'INT');

输出

结果

8

询问是针对属性值的。问题是默认名称空间不适用于属性，因此您必须显式添加它。

DECLARE @xml XML = 
N'<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="9/15/2021 11:38:35 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="X" DTS:CreatorName="Y" DTS:DTSID="{5BDDA1D9-F546-47BC-81BB-E29C0E22D5DA}"
                DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="16.0.694.0" DTS:LocaleID="1033" DTS:ObjectName="Mine" DTS:PackageType="5" DTS:VersionBuild="195" DTS:VersionGUID="{68B021E9-5664-4E2B-A64D-C8B5CAF38316}">
    <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
    <DTS:ConnectionManagers>
        <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[A]" DTS:CreationName="OLEDB" DTS:DTSID="{AE299D88-1DB8-48B1-8548-31BE5C2F2721}" DTS:ObjectName="A">
            <DTS:PropertyExpression DTS:Name="InitialCatalog">@[User::A]</DTS:PropertyExpression>
            <DTS:PropertyExpression DTS:Name="ServerName">@[User::B]</DTS:PropertyExpression>
            <DTS:ObjectData>
                <DTS:ConnectionManager DTS:Retain="True" DTS:ConnectRetryCount="1" DTS:ConnectRetryInterval="5"
                                       DTS:ConnectionString="Data Source=XXX;Initial Catalog=XXX;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Application Name=XXXXX;"/>
            </DTS:ObjectData>
        </DTS:ConnectionManager>
    </DTS:ConnectionManagers>
</DTS:Executable>';

;WITH XMLNAMESPACES(DEFAULT 'www.microsoft.com/SqlServer/Dts', 'www.microsoft.com/SqlServer/Dts' as DTS)
SELECT result = @xml.value('(/Executable/Property/@DTS:Name)[1]', 'varchar(200)');