Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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. :

enter image description here

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?

share|improve this question
add comment

1 Answer

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)
}
share|improve this answer
    
Thank you, I learned a lot from your answer.:) –  pansal Aug 3 '11 at 10:50
    
I tried to use get-member to list $row, I am sure $row has got what I need now. But how can I show the content form it?'StageDesc Property System.String StageDesc {get;set;} ', I can see that stagedesc is a property now, how to use it? –  pansal Aug 4 '11 at 6:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.