CODEWITHSUNDEEP
sqlpowershellxml
pansal
pansal

Reputation: 23

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.

The Row named StageDesc contents 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.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?

Upvotes: 0

Views: 1564

Answers (3)

user6645951
user6645951

Reputation: 1

$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).

Upvotes: 0

mailq
mailq

Reputation: 407

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?

Upvotes: 0

Chad Miller
Chad Miller

Reputation: 41847

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}

Upvotes: 2

Related Questions