PowerShell script to query xml tag attribute in database

Question

I want to write a query in powerShell which checks an attribute value in an xml column called(xml_multiple) and return boolean value 1 if it exist(otherwise 0) and pass it to a variable and call a sendemail function. According to the value of variable the email will be sent. 1- for success 0- for failure

I'm new to powershell and not very good at it. I'm open to suggestion as long it works.Thanks in advance. Check the code below and xml

$dataSource = "DB.abc.com"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = “ ” <#here i want to write my query#>
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$connection.Close()

function sendemail()
{

$Outlook = New-Object -ComObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.To = "[email protected]"
 if ($send -eq 1) <#here i want to pass value from db#>
    {
     $Mail.Subject = "Process Successful"
     $Mail.Body ="Success`n`nThank you"
    }
 else
   {
    $Mail.Subject = "Process Unsuccessful"
    $Mail.Body ="Unsuccess`n`nPlease look into it"
   }
$Mail.Send()
}

Note: This is the xml and if any xml has a attribute start="1" return 1 else 0. for a particular day(There will be only one record in a day which will have this attribute,so we can use a filter in the query for that)

<jobparameters start="1">
<work>1
</work>
</jobparameters>

P

lease give suggestions

Andrei Odegov · Accepted Answer · 2017-10-16 04:31:57Z

1

It is possible to get the needed results directly from SQL Server:

DECLARE @x XML = '
<jobparameters start="1">
<work>1
</work>
</jobparameters>';
DECLARE @t TABLE (xml_multiple XML);
INSERT @t(xml_multiple) VALUES(@x);
SELECT c.value('@start','INT') send
FROM @t
OUTER APPLY xml_multiple.nodes('/jobparameters')x(c);

answered Oct 16, 2017 at 4:31

Andrei Odegov

3,5722 gold badges18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell script to query xml tag attribute in database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related