How to display xml content from sql table by powershell?

What I need to do are: 1, query a row of xml from a sql server datatable. See pic below,the Row named StageDesccontents xml file.

2, the xml file contents a path //sharespace/test1/10.0.1212.0which I need to get, this was forming as<releasepath>//sharespace/test1/10.0.1212.0</releasepath> in the xml file.

Here are my codes try to get it:

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlQuery = "SELECT Stage.Description as StageDesc,Stage.StageStatusId FROM [Build].[dbo].[WorkflowInstance_View] as Build
  join [Build].[dbo].[Stage_View] as Stage on Build.开发者_Python百科Id=Stage.[WorkflowInstanceId] where Stage.ParentId is null and Stage.StageStatusId <>4 and Stage.StageStatusId <>7 order by Build.Id desc"

$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $Connection

$DBResult = $sqlcmd.ExecuteReader()

$DataTable = New-Object system.data.datatable

$DataTable.load($DBResult)

foreach ($StageDesc in $DataTable) {

[XML]$ReturnedXML=$StageDesc.releasepath

}

The code passed but returned nothing. Why this happened? Could anybody would like to help me?

---

You're assigning your xml data to a variable $RetrunedXML and overwriting the assignment on each iteration of your foreach. Have you checked $ReturnedXML?

Using the sample database for SQL Server 2008, I can use this:

$serverName = "$env:computername\sql1"
$databaseName = "AdventureWorks"
$query = "SELECT * from Person.Contact where AdditionalContactInfo IS NOT NULL"
$conn=new-object System.Data.SqlClient.SQLConnection
$connString = “Server=$serverName;Database=$databaseName;Integrated Security=SSPI;”
$conn.ConnectionString=$connString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$da = New-Object “System.Data.SqlClient.SqlDataAdapter” ($cmd)
$dt = New-Object “System.Data.DataTable”
$da.fill($dt) | out-null
$conn.Close()

$dt | foreach {[xml]$ReturnedXML = $_.AdditionalContactInfo; $ReturnedXML}

---

All you do in the code is declaring and assigning variables. There is no code that outputs or displays anything. Nor do you return any variable. So what do you expect the code should return? In which line? Did you even try to debug the code?

---

$da.fill($dt)

Loads the query results into DataTable $dt.

$dt | Out-GridView

Shows all the data.

The script worked great for me (except the last line, which didn't apply for my case).