How can I get the attribute value through Select-X

How can I get an attribute value using Select-Xml?

Here is a fragment of an SSIS .dtsx file.

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
  DTS:refId="Package"
  ...
  <DTS:ConnectionManagers>
    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[DW02.EDW_Source]"
      DTS:CreationName="OLEDB"
      DTS:DTSID="{12F8E4D7-B122-40AF-A3BD-2B283F9EB3A0}"
      DTS:ObjectName="DW02.EDW_Source">

The following code does not produce the desired result. How can I get the attribute value?

$x = Get-Content -Path .\ECW_SPECIALITY.dtsx
$namespace = @{DTS='www.microsoft.com/SqlServer/Dts'}
$x | Select-Xml -XPath '//@ConnectionManagers/@ConnectionManager[@DTS:ObjectName]' -Namespace $namespace

I'm getting the following error:

Select-Xml : Cannot convert value "<DTS:Executable xmlns:DTS="www.microsoft.com/
SqlServer/Dts"" to type "System.Xml.XmlDocument". Error: "Unexpected end of file
has occurred. The following elements are not closed:  Line 1, position 60."
At line:1 char:6
+ $x | Select-Xml -XPath '//@ConnectionManagers/@ConnectionManager[@DTS ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Select-Xml], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : InvalidCastToXmlDocument,Microsoft.PowerShell.Commands.SelectXmlCommand

First and foremost, you're not passing the XML data as a single string. That's what's causing the error you've observed, because the line <DTS:Executable ... is not valid XML by itself.

Either read the file into a single string:

$x = Get-Content '.\ECW_SPECIALITY.dtsx' -Raw          # PowerShell v3 or later
$x = Get-Content '.\ECW_SPECIALITY.dtsx' | Out-String  # PowerShell v2 or earlier

or pass the file directly to Select-Xml:

Select-Xml -Path '.\ECW_SPECIALITY.dtsx' -XPath ...

Just fixing that still won't give you the desired result, though, since your XPath expression is incorrect.

For selecting namespaced nodes or attributes the prefix defined in the namespace hashtable must be used in the XPath expression.
@ indicates an attribute. It must not be used for nodes.
Square brackets define criteria by which the selected nodes/attributes are filtered, not nodes/attributes to be selected. You need this for selecting a node that has a particular attribute, but not when you want to select the attribute itself.
The output of Select-Xml gives you the selected node or attribute (property Node) along with the input item (property Path) and the XPath expression (property Pattern). To get the value of the node/attribute you must expand the property Node. Twice.

This should do what you want:

$ns    = @{DTS='www.microsoft.com/SqlServer/Dts'}
$path  = '.\ECW_SPECIALITY.dtsx'
$xpath = '//DTS:ConnectionManagers/DTS:ConnectionManager/@DTS:ObjectName'
#           ^node                  ^node                 ^attribute

Select-Xml -Path $path -XPath $xpath -Namespace $ns |
    Select-Object -Expand Node |
    Select-Object -Expand '#text'

For further details please check the XPath reference.