CODEWITHSUNDEEP
sqlxmlpowershell
Areeb
Areeb

Reputation: 11

PowerShell script to query xml tag attribute in database

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

Upvotes: 1

Views: 160

Answers (1)

Andrei Odegov
Andrei Odegov

Reputation: 3439

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);

Upvotes: 1

Related Questions