How to display xml content from sql table by powershell?

Question

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?

Chad Miller · Accepted Answer · 2011-08-04 11:46:18Z

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}

EBGreen · Accepted Answer · 2011-08-04 14:12:18Z

0

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?

edited Aug 4, 2011 at 14:12

EBGreen

38k12 gold badges69 silver badges86 bronze badges

answered Aug 4, 2011 at 7:51

mailq

4072 silver badges10 bronze badges

Comments

user8397947 · Accepted Answer · 2016-07-27 18:13:48Z

0

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

edited Jul 27, 2016 at 18:13

user8397947

1,5444 gold badges23 silver badges28 bronze badges

answered Jul 27, 2016 at 16:56

user6645951

1

Collectives™ on Stack Overflow

How to display xml content from sql table by powershell?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related