Export script/SQLQuery result to XML with text

Dear expert, please check below, I checked the update.csv and it works fine, now I want to make this script to export to a xml file with text like: "Web user update" $result "done", everytime I ran that script, and xml file name can be random. The $result is from SqlQuery.

Thanks

$SQLServer = "test\user"
$SQLDBName = "web"


$outfile = "c:\Test\update.csv"

$SqlQuery = "
SELECT count(*)
FROM [test3].[Data].[IncomingUpdate]
WHERE InsertTime >= DATEADD(day,-10, getdate())
"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet)
 
$SqlConnection.Close()
$sqlArr | export-csv -NoTypeInformation -Delimiter ";" $outfile -encoding default

Open in new window

LVL 1
WeTiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose Gabriel Ortega CEE Solution Guide - CEO Faru Bonon ITCommented:
Change the last line:
$sqlArr | export-csv -NoTypeInformation -Delimiter ";" $outfile -encoding default

Open in new window


for:
ConvertTo-Xml -InputObject $sqlArr | Out-File -FilePath "here.xml"

Open in new window


and there's a PowerShell command that runs directly from PowerShell, you do not need to do the translation to C#.

Invoke-Sqlcmd (Here's the documentation: https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
thanks
0
oBdACommented:
WeTi,
please do yourself a favor and pretend you know absolutely nothing about the background of your question (or ask a colleague knowing nothing about this script), and ask yourself/him whether this question can be properly answered based on the sparse information you provided.
You're just dropping a PowerShell script here, without any explanation about what it will do.
Based on that, you're expecting us to "export to a xml file". XML is a wide field, and you're supplying absolutely no information about the XML's format you need - not even that it can be any format that comes to our mind, if that might be what you want.
The script is obviously stripped down and won't work as posted:
- the connection will fail, as you removed either the explicit SQL logon information, or the "Integrated Security=True" from the connection string, without indicating what you did and what someone trying to understand what's happening should know to make it work.
- the $sqlArr variable you're exporting is never set in your script.
Note that it is in your own best interest to provide enough and correct details from the get-go for us to be able to help you. Vital information was very obviously missing from your question, which is why I ignored it when you posted it (I'm now here on special invitation of Scott Fell ...) - if you can't even be bothered to formulate a fully fleshed out question, why should anyone of the people supposed to help you be motivated enough to analyze your script stub, try to read your mind, and formulate a fully fleshed out answer?
In IT related terms: it is your obligation to push all the relevant information, not our obligation to pull/query for it.

Now, sorry about that rant, but again: it is in your own best interest to write good questions if you want to get good answers.

That said, it seems that all your query will return is a scalar, that is, the number of rows returned from your query.
Your csv file for a query returning 42 probably looks like this:
Column1
42

Open in new window

For that, filling a DataSet using SQLAdapter is overkill and makes the script hard to understand; a simple ExecuteScalar() will do it.
And if you want to embed long strings in a PS script, use a Here-String (@" ... "@; see https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_quoting_rules?view=powershell-6), not just a string spanning multiple lines; this avoids nasty surprises if your string contains the quote that opened it.
This will now use ExecuteScalar() and create a simple xml instead of the csv.
$SQLServer = "test\user"
$SQLDBName = "web"
$xmlFile = "c:\Test\update.xml"
$SqlQuery = @"
	SELECT count(*)
	FROM [test3].[Data].[IncomingUpdate]
	WHERE InsertTime >= DATEADD(day,-10, getdate())
"@

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server='$($SQLServer)'; Database='$($SQLDBName)'; "

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlConnection.Open()
$Count = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()

"Updates found: $($Count)"
$xml = [xml]'<root />'
$resultNode = $xml.SelectSingleNode('root').AppendChild($xml.CreateElement("result"))
$resultNode.InnerText = "Web user update $($Count) done"
$xml.Save($xmlFile)
"XML:"
$xml.InnerXml

Open in new window



Jose,
the methods used in the script are .NET, not C#, and they have the unbeatable advantage that they will run on basically any Windows OS with .NET 1.1 or later, without having to install anything.
0
WeTiAuthor Commented:
I understand your comment there, oBdA this question was asked when I didn't know too much of powershell code and XML exporting, (Now Im still learning) so Im sorry if you think the question was not complete enough. I would like to thanks you and all the Expert-Exchange experts for the all the helps I got and hope it keeps that way for the future. Thanks.
0
WeTiAuthor Commented:
I added  Write-Progress -Activity "Counting files" -Status "File of" $obj.Count -PercentComplete (($obj.Count) * 100)
It doesn't work as I see, but it should work, cause its counting the files in $obj?

function Runscript {
  Param(
	[Parameter(Position=0, Mandatory=$True)][ValidateNotNullOrEmpty()]
	[String]$Path,
	[Parameter(Position=1, Mandatory=$True)][ValidateNotNullOrEmpty()]
	[String]$string
  )
 
    foreach ($result in (Get-ChildItem -Path $Path -Recurse -File | Select-String -Pattern $string)) {
        $obj = New-Object -TypeName PSObject
        $obj | Add-Member -MemberType NoteProperty -Name LWT -Value ((Get-Item -Path $result.path).LastWriteTime)
        $obj | Add-Member -MemberType NoteProperty -Name Path -Value $result.path
        $obj | Add-Member -MemberType NoteProperty -Name Line -Value $result.linenumber
        ForEach-Object {
        $obj | Select-Object Path, Line, LWT
        Write-Output $obj
        }
    Write-Progress -Activity "Counting files" -Status "File of" $obj.Count -PercentComplete (($obj.Count) * 100)
    }
    if ($result -eq $null) {write-host "Can not found "$Text}
}

do {
    Runscript | out-host
    $response = read-host 'Search again?'
} until ($response -ne 'Yes')

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.