A question about how to display table contents by using powershell from sql database

Question

I am writing a script aim to get some info of running complie process from sql database.

If I ran the following line as a query in sql server

 SELECT Stage.Description as StageDesc,Stage.StageStatusId FROM [Build].[dbo].[WorkflowInstance_View] as Build join [Build].[dbo].[Stage_View] as Stage on Build.Id=Stage.[WorkflowInstanceId]where Stage.ParentId is null and Stage.StageStatusId <>4 and Stage.StageStatusId <>7 order by Build.Id desc 

I can get a table result like this,which contents xml informations. :

Now, Here are my codes by powershell, I was aim to get the xml part:

$connection= new-object system.data.sqlclient.sqlconnection
$Connection.ConnectionString ="server=SQLShare;database=Build;trusted_connection=True"
$connection.open()

$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.Id=Stage.[WorkflowInstanceId]where Stage.ParentId is null and Stage.StageStatusId <>4 and Stage.StageStatusId <>7 order by Build.Id desc"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 
$Connection.Close()

but the result only appeared '2', this is the number of current running complie process.

Since I am sooo ignorent of sql server. Could any body would like to how to do this? How can I get the xml part by using powershell?

Answer 1

I've done this kind of thing fairly often. Not the XML part, but pulling data out SQL via Powershell.

$SqlQuery = "SELECT Thing, Item, FROM Tablename WHERE (Item < "5")"
$SqlCmd.CommandTest=$SqlQuery
$DBResult = $DBComand.ExecuteReader()
$DataTable = New-Object system.data.datatable
$DataTable.load($DBResult)

You now have an object of type Datatable to parse through.

foreach ($Row in $DataTable) {
    [XML]$ReturnedXML=$Row.Thing()
    DoSomethingWithXML($ReturnedXML)
}