Better late than never :w00t:

Here is an example;

DECLARE @DTSXML XML = N'<?xml version="1.0"?>

<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"

DTS:refId="Package"

DTS:CreationDate="11/7/2013 12:57:04 PM"

DTS:CreationName="SSIS.Package.3"

DTS:CreatorComputerName="USER-COMPUTER"

DTS:CreatorName="MyUserName"

DTS:DTSID="{10A38C50-3656-42E0-A054-87E8D9DFBD8A}"

DTS:ExecutableType="SSIS.Package.3"

DTS:LastModifiedProductVersion="11.0.2100.60"

DTS:LocaleID="2057"

DTS:ObjectName="TEST_RELOAD_PACKAGE_DDL_TEST_RELOAD"

DTS:VersionGUID="{9C72489E-4800-4E59-9666-6B3FA0A664F9}">

<DTS:Property

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

<DTS:ConnectionManagers>

<DTS:ConnectionManager

DTS:refId="Package.ConnectionManagers[DESTINATION_CONN]"

DTS:CreationName="OLEDB"

DTS:DTSID="{1FD5677D-2669-41DB-83DB-CB8FE1C89246}"

DTS:ObjectName="DESTINATION_CONN">

<DTS:ObjectData>

<DTS:ConnectionManager />

</DTS:ObjectData>

</DTS:ConnectionManager>

<DTS:ConnectionManager

DTS:refId="Package.ConnectionManagers[EXEC_CONN]"

DTS:CreationName="OLEDB"

DTS:DTSID="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"

DTS:ObjectName="EXEC_CONN">

<DTS:ObjectData>

<DTS:ConnectionManager

DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />

</DTS:ObjectData>

</DTS:ConnectionManager>

<DTS:ConnectionManager

DTS:refId="Package.ConnectionManagers[LOG_CONN]"

DTS:CreationName="OLEDB"

DTS:DTSID="{9D9716EF-0E06-4BDF-89DA-B8EA03A0871F}"

DTS:ObjectName="LOG_CONN">

<DTS:ObjectData>

<DTS:ConnectionManager

DTS:ConnectionString="Data Source=MyServerInstance;Initial Catalog=MyDatabaseInstance;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SR_PROC_CTRL;" />

</DTS:ObjectData>

</DTS:ConnectionManager>

</DTS:ConnectionManagers>

<DTS:LogProviders>

<DTS:LogProvider

DTS:ConfigString="LOG_CONN"

DTS:CreationName="DTS.LogProviderSQLServer"

DTS:DTSID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}"

DTS:ObjectName="LogToSQLServer">

<DTS:ObjectData>

<InnerObject />

</DTS:ObjectData>

</DTS:LogProvider>

</DTS:LogProviders>

<DTS:Variables />

<DTS:LoggingOptions

DTS:FilterKind="0"

DTS:LoggingMode="1">

<DTS:Property

DTS:DataType="8"

DTS:Name="EventFilter">4,12,OnTaskFailed,10,OnProgress,7,OnError,9,OnWarning</DTS:Property>

<DTS:SelectedLogProviders>

<DTS:SelectedLogProvider

DTS:InstanceID="{81B7AF3A-22F7-4FC8-A3F8-9034F8B241A0}" />

</DTS:SelectedLogProviders>

</DTS:LoggingOptions>

<DTS:Executables>

<DTS:Executable

DTS:refId="Package\EXEC__SRSTG___USP_HOTELS_"

DTS:CreationName="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DTS:DTSID="{7401EF27-6D72-4E6A-A294-D88CDA1176B6}"

DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DTS:LocaleID="-1"

DTS:ObjectName="EXEC__SRSTG___USP_HOTELS_"

DTS:ThreadHint="0">

<DTS:Variables />

<DTS:ObjectData>

<SQLTask:SqlTaskData

SQLTask:Connection="{B81FCD2E-0AA9-45F3-8C8C-39675857B93F}"

SQLTask:SqlStatementSource="SELECT * FROM [SRSTG].[TBL_HOTELS];" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />

</DTS:ObjectData>

</DTS:Executable>

</DTS:Executables>

</DTS:Executable>'

;WITH XMLNAMESPACES(

'www.microsoft.com/SqlServer/Dts' AS DTS,

DEFAULT 'www.microsoft.com/SqlServer/Dts'

)

SELECT

EXN.R.value('@DTS:refId' ,'NVARCHAR(128)') AS PKG_refId

,EXN.R.value('@DTS:CreationDate' ,'DATETIME2(0)' ) AS PKG_CreationDate

,EXN.R.value('@DTS:CreationName' ,'NVARCHAR(128)') AS PKG_CreationName

,EXN.R.value('@DTS:CreatorComputerName' ,'NVARCHAR(128)') AS PKG_CreatorComputerName

,EXN.R.value('@DTS:CreatorName' ,'NVARCHAR(128)') AS PKG_CreatorName

,EXN.R.value('@DTS:DTSID' ,'NVARCHAR(128)') AS PKG_DTSID

,EXN.R.value('@DTS:ExecutableType' ,'NVARCHAR(128)') AS PKG_ExecutableType

,EXN.R.value('@DTS:LastModifiedProductVersion' ,'NVARCHAR(128)') AS PKG_LastModifiedProductVersion

,EXN.R.value('@DTS:LocaleID' ,'NVARCHAR(128)') AS PKG_LocaleID

,EXN.R.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS PKG_ObjectName

,EXN.R.value('@DTS:VersionGUID' ,'NVARCHAR(128)') AS PKG_VersionGUID

,EXN.R.value('local-name(.)' ,'NVARCHAR(128)') AS LName

,EXN.R.value('.','NVARCHAR(MAX)') AS LValue

,CON.MAN.value('@DTS:refId' ,'NVARCHAR(128)') AS CNM_refId

,CON.MAN.value('@DTS:CreationName' ,'NVARCHAR(128)') AS CNM_CreationName

,CON.MAN.value('@DTS:DTSID' ,'NVARCHAR(128)') AS CNM_DTSID

,CON.MAN.value('@DTS:ObjectName' ,'NVARCHAR(128)') AS CNM_ObjectName

FROM @DTSXML.nodes('/DTS:Executable') AS EXN(R)

OUTER APPLY EXN.R.nodes('DTS:ConnectionManagers/DTS:ConnectionManager') AS CON(MAN);