CODEWITHSUNDEEP
powershell
Oxyauna
Oxyauna

Reputation: 75

Check if XML Node is true, and if so, execute a SQL statement

I'm working on a script that reads from an XML list of environments and reiterates through each entry, restores a database, runs schema changes on the database, and then backs up said database. I have all of these pieces working, but I'd like to add logic to the script to include a step to set the DB Owner before running the schema changes if the XML node exists;

Currently, if I run this, it writes 'No DBOwner Specified' no matter what.

$dbOwnerExists = $($Environment.Backup.DatabaseBackup.DBOwner)

if ($dbOwnerExists -eq $true) {
    Invoke-Sqlcmd -ServerInstance "$($DatabaseBackup.Instance)" -Query "EXEC sp_changedbowner $($Environment.Backup.DatabaseBackup.DBOwner)"
} else {
    Write-Output 'No DBOwner Specified'
}

Current XML Looks like this

<Environments>

    <Environment Database="exampleDatabase">
        <Backup Restore="TRUE">
            <DatabaseBackups Instance="SQLInstance" SrcLocation="\\Hostname\c$\temp\example.bak" DstLocation="\\Hostname\c$\temp\example.bak" DBOwner="&quot;sa&quot;"/>
            <DatabaseBackups Instance="SQLInstance" SrcLocation="\\Hostname\c$\temp\example2.bak" DstLocation="\\Hostname\c$\temp\example2.bak"/>
        </Backup>
    </Environment>

</Environments>

It seems that powershell doesn't like my logic to check if DBOwner exists in the XML, maybe $true is not the best way to check this?

Upvotes: 0

Views: 360

Answers (2)

Nico Nekoru
Nico Nekoru

Reputation: 3112

I haven't seen your full code so this is the best I can do.

Let's assume that you have the XML stored in variable $XML and $XML was defined like this:

[xml]$xml = @"
<Environments>

    <Environment Database="exampleDatabase">
        <Backup Restore="TRUE">
            <DatabaseBackups Instance="SQLInstance" SrcLocation="\\Hostname\c$\temp\example.bak" DstLocation="\\Hostname\c$\temp\example.bak" DBOwner="&quot;sa&quot;"/>
            <DatabaseBackups Instance="SQLInstance" SrcLocation="\\Hostname\c$\temp\example2.bak" DstLocation="\\Hostname\c$\temp\example2.bak"/>
        </Backup>
    </Environment>

</Environments>
"@

You can check whether DBOwner exists or not just by checking the value with

$XML.Environments.Environment.Backup.DatabaseBackups.DBOwner

Which with the XML would output:

"sa"

But to get a true/false value on whether it exists or not, you can use the [bool] datatype like you did in your answer:

[bool]$XML.Environments.Environment.Backup.DatabaseBackups.DBOwner

But I found a glitch with this in which it would still output true if the node didn't exist like so:

[bool]$XML.Environments.Environment.Backup.DatabaseBackups.fasdasd
True

so I changed it to this.

[bool][string]$($XML.Environments.Environment.Backup.DatabaseBackups.DBOwner).trim

Which in this case would output:

True

And to put this in an if statement since it is already a boolean you could put it in the in statement like so:

if([bool][string]$($XML.Environments.Environment.Backup.DatabaseBackups.DBOwner).trim){
Invoke-Sqlcmd -ServerInstance "$($DatabaseBackup.Instance)" -Query "EXEC sp_changedbowner $($Environment.Backup.DatabaseBackup.DBOwner)"
} else {
    Write-Output 'No DBOwner Specified'
}

To test if this works we can also try doing this on something that doesn't exist like $XML.Environments.Environment.Backup.DatabaseBackups.SomethingThatDoesntExist which should return false and does like so:

[bool][string]($XML.Environments.Environment.Backup.DatabaseBackups.SomethingThatDoesntExist).trim()
False

To test the if statement we can do

if([bool][string]$($XML.Environments.Environment.Backup.DatabaseBackups.DBOwner).trim){
 Write-Output "Exists"
} else {
    Write-Warning 'No DBOwner Specified'
}

Which would output

Exists

and we could also test if it doesn't exist with

if([bool][string]$($XML.Environments.Environment.Backup.DatabaseBackups.fasdasd).trim){
 Write-Output "Exists"
} else {
    Write-Warning 'No DBOwner Specified'
}

The output would be:

WARNING: No DBOwner Specified

NOTE:
The [bool]s in the if statements are unnecessary since if already converts things to booleans but I just put it in for easier understanding this would work fine too:

if([string]$($XML.Environments.Environment.Backup.DatabaseBackups.fasdasd).trim){
 Write-Output "Exists"
} else {
    Write-Warning 'No DBOwner Specified'
}

Upvotes: 1

Oxyauna
Oxyauna

Reputation: 75

I was able to figure this out with the following, which works well enough for me!

$dbOwnerExists=$null
$dbOwnerExists = $($DatabaseBackup.DBOwner)
if ($dbOwnerExists) {
    Invoke-Sqlcmd -ServerInstance "$($DatabaseBackup.Instance)" -Query "USE $($Environment.Database) EXEC sp_changedbowner '$($DatabaseBackup.DBOwner)'"
} else {
    Write-Output 'No DBOwner Specified'
}

Upvotes: 0

Related Questions