How can I get the attribute value through Select-X

Question

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

Answer 1

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.