XML query against SQL Server SSIS DataProfiler xml file in Powershell does not work. Help?

Question

Hi,

I am trying to query out the attribute values from the xml file attached below. Specifically, I am trying to get the Name, SqlDbType etc. attribute values that are in the "Column" Element Node under"ColumnNullRatioProfile" Node. The xml output file comes as a part of the SQL Server 2008 SSIS DataProfiler Task. My goal is to use Powershell to create a CSV file with selected Attributes that can be loaded into an Excel workbook.

However, I have tried several approaches (see some of them in Method 1 and Method 2 below. I cannot make it work. Any suggestions?

#Save as t.xml on C:\
#-----------------------

<?xml version="1.0" ?> 
 <DataProfile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sqlserver/2008/DataDebugger/"&gt;
 <DataSources>
 <DtsDataSource ID="{45277997-59B4-4A95-909E-7804F0761FA1}" Name="DatabaseConn">
  <DtsConnectionManagerID>DatabaseConn</DtsConnectionManagerID> 
  </DtsDataSource>
 <DtsDataSource ID="{BAEE1FCA-E5A2-4C3C-A1B6-100B3B681397}" Name="Table.xml">
  <DtsConnectionManagerID>Table.xml</DtsConnectionManagerID> 
  </DtsDataSource>
  </DataSources>
 <DataProfileOutput>
 <Profiles>
 <ColumnNullRatioProfile ProfileRequestID="NullRatioReq" IsExact="true">
    <DataSourceID>{45877997-59B4-4A95-909E-7804F0761FA1}</DataSourceID> 
    <Table DataSource="XVRTFD0585\SQL905" Database="BusinessData" Schema="General" Table="Email_Notifications_Lookup" RowCount="-1" /> 
    <Column Name="EmailURL_ID" SqlDbType="Int" MaxLength="0" Precision="10" Scale="0" LCID="-1" CodePage="0" IsNullable="false" StringCompareOptions="0" /> 
    <NullCount>0</NullCount> 
  </ColumnNullRatioProfile>
 <ColumnNullRatioProfile ProfileRequestID="NullRatioReq1" IsExact="true">
        <DataSourceID>{45CC99B2-E396-4CFA-A1F5-4E703F04E9E7}</DataSourceID>
        <Table DataSource="XVRTFD0585\SQL905" Database="BusinessData" Schema="General" Table="LOOKUP_CODES" RowCount="5979114" />
        <Column Name="TRANS_ID" SqlDbType="Decimal" MaxLength="0" Precision="9" Scale="0" LCID="-1" CodePage="0" IsNullable="true" StringCompareOptions="0" />
        <NullCount>5979114</NullCount>
      </ColumnNullRatioProfile>
  </Profiles>
  </DataProfileOutput>
  </DataProfile>


#Method 1
#--------

$uri="C:\t.xml"
$xDoc = [xml](Get-Content $uri ) 
$XDoc.DataProfile.DataProfileOutput.Profiles.ColumnNullRatioProfile.Column | select Name

#Method 2 (Using LINQ)
#--------

$uri="C:\t.xml"
[Reflection.Assembly]::LoadWithPartialName(”System.Xml.Linq”) | Out-Null
$XDoc = [System.Xml.Linq.XDocument]::Load($uri)
$XDoc.Descendants(“ColumnNullRatioProfile”) | ForEach {$_.Element("Column”).GetAttribute("Name").Value}

Answer 1

$XDoc.DataProfile.DataProfileOutput.Profiles.ColumnNullRatioProfile | % {$_.column} | select name

Answer 2

$file = [xml](gc "C:\t.xml")

$columns = $file.DataProfile.ColumnNullRatioProfile.Column.name