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

Question

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?

Try `if(!($Environment.Backup.DatabaseBackup.DBOwner))` to check if it doesn't exist... Is the variable called `$Environment`? To check if it exists rather than not exists you could also do `[bool]$XML.Environments.Environment.Backup.DatabaseBackups.DBOwner` — Nico Nekoru, Jun 15 '20 at 20:05

Nico Nekoru · Accepted Answer · 2020-06-15T21:15:31.567

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'
}

score 0 · Answer 2 · answered Jun 15 '20 at 20:34

0

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'
}

answered Jun 15 '20 at 20:34

Oxyauna

75
2
9

1

I found a glitch with this while I was testing that makes this not work. If you do something that doesn't exist with that exact node `Environments.Environment.Backup.DatabaseBackups` it will print true no matter the thing like `[bool]$XML.Environments.Environment.Backup.DatabaseBackups.asdfasdfdas` which would output `True`. I don't knwo why this is, but I found a way around it in my answer. – Nico Nekoru Jun 15 '20 at 21:08

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

2 Answers2