Author name: Tim van Kooten Niekerk

XML Transformation Example

/ XML /

The following example transforms an XML file to a different format, by using an XSLT (transformation-stylesheet).

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
 
  <xsl:template match="/">
    <!--Use apply-templates to create a loop...-->
    <website>
      <xsl:apply-templates select="manuals/manual"/>
    </website>
  </xsl:template>

  <xsl:template match="manuals/manual">
    <article>
      <title>
        <!--id, title-->
        <xsl:value-of select="concat(id,' - ',title)"/>
      </title>
      <writer>
        <!--written_by-->
        <xsl:value-of select="written_by"/>
      </writer>
      <category>
        <!--category-->
        <xsl:choose>
          <xsl:when test="category='GNU/Linux'">GNU</xsl:when>
          <xsl:when test="category='MS Windows Server'">MSOS</xsl:when>
          <xsl:when test="category='MSSQL Server'">MSSQL</xsl:when>
          <xsl:when test="category='MySQL Server'">MYSQL</xsl:when>
          <xsl:when test="category='Netwerk'">NET</xsl:when>
          <xsl:when test="category='PowerShell Scripting'">MSPSH</xsl:when>
          <xsl:when test="category='VisualBasic Scripting'">MSWSH</xsl:when>
          <xsl:when test="category='XML'">XML</xsl:when>
          <xsl:otherwise>Category unknown</xsl:otherwise>
        </xsl:choose>
      </category>
    </article>
  </xsl:template>

</xsl:stylesheet>

An example of an input and output file based on above XSLT transformation.

INPUT:
<?xml version="1.0" encoding="UTF-8"?>
<manuals xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <manual>
    <title>PowerShell Unzip Function</title>
    <written_by>Tim van Kooten Niekerk</written_by>
    <category>PowerShell Scripting</category>
    <id>78</id>
    <year>2013</year>
  </manual>
</manuals>

OUTPUT:
<?xml version="1.0" encoding="UTF-8"?>
<website>
  <article>
    <title>78 - PowerShell Unzip Function</title>
    <writer>Tim van Kooten Niekerk</writer>
    <category>MSPSH</category>
  </article>
</website>

 

GNU/Linux RegExp basics

/ GNU/Linux /

RegExp basic encoding…

? = 0..1
* = 0..n
NULL  = 1..1
+ = 1..n
| = XOR
{3} = 3
{2,8} = 2-8 chars/numbers
^ = Matches beginning
$ = Matches end

Example RegExp

X-[0-9]?[0-9]-((0[1-9])|([1-2][0-9])|(3[0-2]))-[A-Z]{3}[0-9]{3,6}

Matching and Not-matching...
X-88-32-ABC123 -- MATCH
X-7-06-XYZ12345 -- MATCH
X-88-33-ABC123 -- NOTMATCH (33 not within 01-32)
X-88-32-ABC12 -- NOTMATCH (12 not 3-6 numbers)

XML Schema Example

/ XML /

A simple XML schema (XSD) sample with Complex Types and restrictions on data types. The schema is built up from bottom to top.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://totietoot.nl/schemas/example" elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="http://totietoot.nl/schemas/example">
  <xs:simpleType name="titleType">
    <xs:restriction base="xs:string">
      <xs:maxLength value="50"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="written_byType">
    <xs:restriction base="xs:string">
      <xs:maxLength value="30"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="categoryType">
    <xs:restriction base="xs:string">
      <xs:enumeration value="GNU/Linux"/>
      <xs:enumeration value="MS Windows Server"/>
      <xs:enumeration value="MSSQL Server"/>
      <xs:enumeration value="MySQL Server"/>
      <xs:enumeration value="Netwerk"/>
      <xs:enumeration value="PowerShell Scripting"/>
      <xs:enumeration value="VisualBasic Scripting"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="companyType">
    <xs:restriction base="xs:string">
      <xs:maxLength value="15"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="idType">
    <xs:restriction base="xs:integer"/>
  </xs:simpleType>
  <xs:simpleType name="yearType">
    <xs:restriction base="xs:integer">
      <xs:minInclusive value="1950"/>
      <xs:maxExclusive value="2050"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:complexType name="manualComplexType">
    <xs:sequence>
      <xs:element name="title" type="titleType"/>
      <xs:element name="written_by" type="written_byType"/>
      <xs:element name="category" type="categoryType"/>
      <xs:element name="id" type="idType"/>
      <xs:element name="year" type="yearType"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="manualType">
    <xs:sequence maxOccurs="unbounded">
      <xs:element name="manual" type="manualComplexType"/>
    </xs:sequence>
  </xs:complexType>
  <xs:element name="manuals" type="manualType"/>
</xs:schema>

XML example based upon above schema.

<?xml version="1.0" encoding="UTF-8"?>
<manuals xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://totietoot.nl/schemas/example" xsi:schemaLocation="http://totietoot.nl/schemas/example http://totietoot.nl/schemas/example/example.xsd">
  <manual>
    <title>PowerShell Unzip Function</title>
    <written_by>Tim van Kooten Niekerk</written_by>
    <category>PowerShell Scripting</category>
    <id>78</id>
    <year>2013</year>
  </manual>
</manuals>

GNU/Linux WGET Auth

/ GNU/Linux /

With the wget example below, you can download a file when HTTP auth is required.

wget --http-user=<username> --http-password=<password> --ca-certificate='chain1.pem' 'https://www.example.com/site/file.bin' -O file.bin

Form-based authenticatie wget example using a session cookie…

wget --post-data='UserName=<username>&Password=<password>' --ca-certificate='chain2.pem' --cookies=on --keep-session-cookies --save-cookies=cookie.txt 'https://login.example.com/auth' -O result.txt

wget --referer='https://login.example.com/auth' --ca-certificate='chain2.pem' --cookies=on --keep-session-cookies --load-cookies=cookie.txt 'https://www.example.com/site/file.bin' -O file.bin

PowerShell TCP Socket Function

/ PowerShell Scripting /

The PowerShell function example below can be used to transmit information using the TCP protocol. For example, to perform an HTTP GET command on a website.

function fnTCPSock([string]$sHostName, [string]$sPort, [string]$sScript) {
  # Converteer script string to byte for write stream...
  [byte[]]$byteScript = [System.Text.Encoding]::ASCII.GetBytes($($sScript))
  # Open 4096 Bytes buffer for read stream...
  [byte[]]$byteBuffer = New-Object System.Byte[] 16384
  
  # Connect to host...
  $oTCPSock = New-Object System.Net.Sockets.TcpClient($sHostName, $sPort)
  $oStream = $oTCPSock.GetStream() 
  
  # Feed script(byte) tot write stream...
  $oStream.Write($byteScript, 0, $byteScript.Length)

  # Return read buffer converted to string...
  [string]$sCount = $oStream.Read($byteBuffer, 0, 16384)
  return [System.Text.Encoding]::ASCII.GetString($byteBuffer, 0, $sCount) 

  # Close objects...
  $oResult.Close()
  $oSockTCP.Close()
}
[string]$sScript1 = "GET / HTTP/1.1`r`nAccept: text/html`r`nHost: [virtualhostname]`r`nReferer: http://host.domain.name/site/`r`n`r`n`r`n"
[string]$sResult1 = fnTCPSock "hostname" "80" $($sScript1)

MSSQL Table Switch IN/OUT examples

/ MSSQL Server /

MSSQL table switch IN, switch OUT examples…

-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk


-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])


-- Make  table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])


-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number

-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL 
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT] 


-- Now you can reload (INSERT) new values for 2015 in the original table... 
-- Or... Make Switch IN with using a switch IN table for 2015...

 
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] CHECK (jaar = 2015) NOT NULL 
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5


-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)

MSSQL Exchange names 2 databases

/ MSSQL Server /

Using the following Stored Procedure you can swap the names of two databases. For example, to bring a recently updated database online, after which the old database can be filled again. To exchange database names, a third name is required. This is the name of database2 with the addition ‘_OLD’.

/****** Object:  StoredProcedure [dbo].[DbNameExchange]    Script Date: 12/01/2012 09:15:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==============================================================
-- Author:		Tim van Kooten Niekerk
-- Create date: 2012-12-01-A
-- Description:	Exchange database names between 2 databases...
-- ==============================================================

CREATE PROCEDURE [dbo].[DbNameExchange]
    @vDatabase1 nvarchar(256),
    @vDatabase2 nvarchar(256)
    
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @H1 AS int
  DECLARE @vDatabaseT AS nvarchar(256)
  DECLARE @vQuery1 AS nvarchar(512)

  -- T.b.v. wisselen van database namen is er een derde naam nodig...
  SET @vDatabaseT = @vDatabase2 + N'_OLD'

  -- Check rename action prefered state before rename action 1/3...
  IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
  BEGIN
    -- Geef de secundaire db een tijdelijke naam...
    BEGIN TRY
      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase2 + '] MODIFY NAME = [' + @vDatabaseT + '];ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
      EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END TRY
    BEGIN CATCH
    END CATCH
    -- Check rename action prefered state before rename action 2/3...
    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 0
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
    BEGIN
      -- Geef de primaire db de naam van de secundaire db...
      BEGIN TRY
	    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase1 + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
      END TRY
      BEGIN CATCH
      END CATCH
  	  -- Check rename action prefered state before rename action 3/3...
      IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 0
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
      BEGIN
        -- Geef de db met de tijdelijke naam de naam van de primaire db...
        BEGIN TRY
	      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        END TRY
	    BEGIN CATCH
        END CATCH
	    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
	    BEGIN
	      PRINT 'INFO: Rollout actie correct uitgevoerd...'
	    END
	    ELSE BEGIN
          PRINT 'ERROR: Rollout actie is niet volledig uitgevoerd, probeer het nomaals...'
	      BEGIN TRY 
	        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
            EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
          END TRY
	      BEGIN CATCH
          END CATCH
          -- Niet gelukt, dan in ieder geval multi_user activeren...
		  SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
	    END
	  END
    ELSE BEGIN
	    -- Something went wrong trying to rollback...
	    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
  	  END
    END
    ELSE BEGIN
    -- Something went wrong trying to rollback...
    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
    EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END
    -- Unprepare statements...
    EXEC sp_unprepare @H1
  END
  ELSE BEGIN
    PRINT 'ERROR: Startsituatie niet correct...'
  END
END

GO