Join two XML files using XSL Transformation

This blog demonstrates how you can JOIN (yes, like in SQL) two xml (datasets) to one using a common ID (relationship).

shop.xml

[code lang=”xml”]
<?xml version=”1.0″ encoding=”UTF-8″?>
<?xml-stylesheet type=’text/xsl’ href=’main.xsl’?>
<shop>
<product>
<id>100</id>
<title>hello</title>
</product>

<product>
<id>101</id>
<title>world</title>
</product>
<product>
<id>102</id>
<title>praveen</title>
</product>
</shop>
[/code]

price.xml

[code lang=”xml”]

<?xml version=”1.0″ encoding=”UTF-8″?>
<shop>
<product>
<id>100</id>
<price>10.0</price>
</product>
<product>
<id>101</id>
<price>10.1</price>
</product>
<product>
<id>102</id>
<price>10.2</price>
</product>
<product>
<id>103</id>
<price>10.3</price>
</product>

</shop>
[/code]

main.xsl

[code lang=”xml”]
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”yes” />

<xsl:param name=”fileName” select=”‘price.xml'” />

<xsl:variable name=”updateItems” select=”document($fileName)/shop/product” />

<xsl:template match=”@*|node()”>
<xsl:copy>
<xsl:apply-templates select=”@*|node()” />
<xsl:copy-of select=”$updateItems[id=current()/id]/price” />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
[/code]

Resulting XML, after transformation should look like:

[code lang=”xml”]
<?xml version=”1.0″ encoding=”utf-8″?>
<?xml-stylesheet type=’text/xsl’ href=’main.xsl’?>
<shop>
<product>
<id>100</id>
<title>hello</title>
<price>10.0</price>
</product>
<product>
<id>101</id>
<title>world</title>
<price>10.1</price>
</product>
<product>
<id>102</id>
<title>praveen</title>
<price>10.2</price>
</product>
</shop>
[/code]

Bonus code, if some .net developers want a transformation code:

[code lang=”csharp”]
XslTransform xslt = new XslTransform();
xslt.Load(@”D:\Websites\xmltest\main.xsl”);
xslt.Transform(@”D:\Websites\xmltest\shop.xml”, @”D:\Websites\xmltest\out.xml”);
textBox1.Text = System.IO.File.ReadAllText(@”D:\Websites\xmltest\out.xml”);
[/code]

Programming Puzzle #2 – Leet Converter

Write a program in a computer language of your choice to convert any given text to “leet format” in real time.

Leet (or “1337”), is a system of modified spellings used primarily on the Internet.

Input: “Translator” Output:”Tr4nsl4t0r”
Input: “leet”, Output: “l33t”
Input: “Good Morning”, Output: “G00d M0rn1ng”

Evaluation criteria:

  1. Code Quality Standards
  2. OOAD/Object-Oriented Analysis & Design
  3. Application Logic
  4. Exception Handling
  5. Simplicity and Effectiveness of code

Time: 0-30 minutes max.

Programming Puzzle #1–Find the critical path

Write a program in a language of your choice to find the critical path from a given set of tasks.

A critical path is determined by identifying the longest stretch of dependent activities and measuring the time required to complete them from start to finish.

image

Each circle (A-G) are tasks with specific duration (in Hours).

Input:

Array of task names and duration given in the diagram.

Output

1. Longest path (Critical path) is A+G+B+F+C+D (42Hrs)
2. Shortest path is A+B+C+D (26 Hrs)

T-SQL script to create SQL Job for daily database backup

This script will create an SQL Server Agent job, which will take daily database backup to a folder with date appended in the filename.

[code]
DECLARE @job varchar(100) = ‘Backup_testdb_daily’ — Name of Job
DECLARE @db varchar(100) = ‘testdb’ — DB to backup
DECLARE @bakfile varchar(100) = ‘d:\_temp\’ + @db — Backup file path

DECLARE @date varchar(8) = ‘20180720’ — Job Start date
DECLARE @time varchar(8) = ‘135400’ — Job run time. Eg: Run At 23rd hour

——————————————————————–

DECLARE @cmd varchar(200) = CONCAT (‘DECLARE @bakfile varchar(200) = ”’ , @bakfile , ”’ + ”_” + convert(varchar(100),GetDate(),112) + ”.bak”;’) +

CONCAT(‘BACKUP DATABASE ‘, @db, ‘ TO DISK = @bakfile’);

USE msdb

EXEC dbo.sp_add_job
@job_name = @job;

EXEC sp_add_jobstep
@job_name = @job,
@step_name = ‘Backup database’,
@subsystem = ‘TSQL’,
@command = @cmd

EXEC sp_add_jobschedule
@job_name = @job,
@name = ‘DB Backup Schedule’,
@freq_type = 4, — daily
@freq_interval = 1,
@active_start_date = @date,
@active_start_time = @time

EXEC dbo.sp_add_jobserver
@job_name = @job,
@server_name = @@SERVERNAME

[/code]

Get Last Modified Date without downloading the file in PowerShell

Compatibility: v5.x
[code]
$zipfile = “https://www…./xyz/test.zip”

add-type @”
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
“@

$AllProtocols = [System.Net.SecurityProtocolType]’Ssl3,Tls,Tls11,Tls12′
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

#$res = Invoke-WebRequest -Uri $zipfile

$webRequest = [System.Net.HttpWebRequest]::Create($zipfile)
$webRequest.Method = “GET”
[System.Net.HttpWebResponse]$webResponse = $webRequest.GetResponse()

Write-Output $webResponse.LastModified
[/code]

Getting SQL Server result set in PowerShell

Compatibility: v5.x
If you are a .NET developer then you are already familiar with this code Smile

[code]
$connectionString = “Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorks2016CTP3”
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter(“SELECT TOP 10 * FROM Person.Address”, $connectionString)
$da.Fill($dt)

Write-Output $dt.Rows[0][“AddressLine1”]

# or

ForEach ($dr in $dt.Rows) {
Write-Output $dr[“AddressLine1”]
}
[/code]

Send an Office365 SMTP mail in .NET/C#

Sending a mail using Office365 is no big deal. It is just similar to how you send a mail via any SMTP server. But you will have to mind the SMTP host, port number and remember to enable SSL in the code. Here goes the code snippet:

[code]
using (MailMessage message = new MailMessage())
{
message.From = new MailAddress(“praveen@YourO365Domain.com”);
message.To.Add(“praveen@yourmail.com”);
message.Subject = “test mail”;
message.Body = “<h1>test</h1>”;
message.IsBodyHtml = true;

SmtpClient client = new SmtpClient(“outlook.office365.com”, 587);

client.Credentials = new NetworkCredential(“praveen@YourO365Domain.com”, “passwordhere”);
client.EnableSsl = true;
client.Send(message);
}
[/code]