CODEWITHSUNDEEP
sqlxmlpowershell
ConfusB
ConfusB

Reputation: 11

Getting variable from XML, parsing it and then forming a SQL query

I have one config.xml file for configuration and another (steps.xml) that acts as a guide and contains steps that generate scripts for execution.

config.xml:

<CFG>
    <Server>sql-server.com</Server>
    <Database>mySQLDB</Database>
</CFG>

steps.xml:

<Steps>
    <Step no="1">
        <script>
          USE [<v>$configs.CFG.Database</v>]
          UPDATE server_name SET name = '<v>$configs.CFG.Server</v>'
        </script>
    </Step>
</Steps>

I use <v></v> to tell my script where the variables are. I don't know if that is a good way to go about.

Then I run this script to parse the files:

$configFile = 'd:\ps\config.xml'
$stepsfile = 'd:\ps\steps.xml'
[xml]$steps = Get-Content -Path $stepsfile 
[xml]$configs = Get-Content -Path $configFile

$t = $Steps.Steps.Step[0].script.v

foreach ($var in $t){
    $exec = Invoke-Expression $var
    # use $exec to form a working script
}

I can resolve the actual information from configuration file but I am stuck at putting it back together. I know I can use this to get full script text:

$Steps.Steps.Step[0].script."#text"

But I am lost how can I pinpoint the location of variables to put resolved names back into it and form a usable script.

The output I'm trying to achieve should be:

USE [mySQLDB]
UPDATE server_name SET name = 'sql-server.com'

Upvotes: 1

Views: 94

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200393

Invoke-Expression is considered harmful. Do not use it.

Instead of using nested XML nodes in the script body you could use any kind of placeholder that normally wouldn't appear in the SQL code (e.g. the names of the nodes from $configs in curly brackets: {Server} and {Database}) and then insert the values via string replacements.

foreach ($step in $steps.SelectNodes('//script')) {
    foreach ($cfg in $configs.SelectNodes('/CFG/*')) {
        $step.'#text' = $step.'#text'.Replace("{$($cfg.Name)}", $cfg.'#text')
    }
}

Upvotes: 1

Related Questions