How to get XML element properties in SQL Server-CodePudding

I am trying to parse XML data from a SSIS package, and I want to get the XML element property values.

<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>

FOR THIS LINE:

 <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>

I want to get the Name="PackageFormatVersion" from the Property element.

How do I do this in T-SQL?

CodePudding user response：

Assuming that the XML is a value of a variable.

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');

Output

result
8

CodePudding user response：

The ask is for the attribute value. And the catch is that the default namespace doesn't apply to attributes, so you have to add it explicitly.

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)');