Author name: Tim van Kooten Niekerk

About me / Hey I’m Tim. I work as a systems integration specialist for a large educational institution in the Netherlands. A part of my free time I spend making and creating music.

MSSQL 2012 New T-SQL Features

/ MSSQL Server /

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1

MSSQL combine SUMS from 2 tables

/ MSSQL Server /

Below query combines the SUMS of two tables into a total SUM for a specific column.

WITH combiTable ([Table], xName, xValue)
AS ( SELECT 1 AS [Table], name, SUM(value) FROM Table1 GROUP BY name
     UNION
     SELECT 2 AS [Table], name, SUM(value) FROM Table2 GROUP BY name )
SELECT xName AS Name, SUM(xValue) AS Total FROM combiTable
GROUP BY xName
ORDER BY Total DESC
GO

XML Stylesheet Example

/ XML /

An XML stylesheet can be used to transform XML file to an HTML table. Check out the results of stylesheet below if it is linked to an XML file. In the source of the page you will only display XML.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >
  
  <xsl:template match="/">
    <html>
    <head><title/></head>
    <body>
      <table border="0pt" style="solid">
        <tbody>
          <!--call table headers-->
          <!--table headers-->
          <tr bgcolor="grey">
            <!--header-->
            <xsl:for-each select="manuals/manual[1]/*">
              <th><xsl:value-of select="name()"/></th>
            </xsl:for-each>
            <th>new-id</th>
          </tr>
          <!--call table rows-->
          <xsl:apply-templates select="manuals/manual">
            <xsl:sort order="ascending" select="title"/>
          </xsl:apply-templates>
        </tbody>
      </table>
    </body>
    </html>
  </xsl:template>

  <xsl:template match="manuals/manual">
    <!--table rows-->
    <!--kleur bepalen-->
    <xsl:variable name="kleur">
      <xsl:choose>
        <xsl:when test="category='PowerShell Scripting'">lightblue</xsl:when>
        <xsl:when test="category='MSSQL Server'">lightpink</xsl:when>
        <xsl:otherwise>white</xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <tr bgcolor="{$kleur}">
    <xsl:for-each select="*">
      <td><xsl:value-of select="."/></td>
    </xsl:for-each>
    <!--extra regel t.b.v. newid-->
    <td><xsl:value-of select="id+1000"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>

MSSQL Select with XML Output

/ MSSQL Server /

The following query returns a result in XML format. the root1 option (root element) adds a root element named “root element” to the file. The option elements creates elements instead of attributes. The auto option creates standard elements with the table name and column names mapped to elements.

SELECT * FROM [tablename] FOR XML auto, root('rootelement'), elements

Another example with a custum (nested) XML layout…

-- Retrieve instancename and other info from database and place info under element intances.instance...
SELECT   i.instance AS [Name]
       , i.edition AS [Edition]
       , i.patchlevel AS [Patchlevel]
       -- Retrieve info from database table on key instance_id place info under child element Databases...
       , ( SELECT   d.[database] AS [Name]
                  , d.RecoveryModel AS [RecoveryModel] 
                  -- Retrieve ref data from OTAP table...
                  , ( SELECT e.OTAP FROM OTAP e WHERE e.ID_OTAP = d.ID_OTAP ) AS [OTAP]
                  -- Retrieve info from application to which this database belongs...
                  , ( SELECT   a.Applicatienaam AS [Name]
                             , a.AuthenticatieModus As [AuthType]
                      FROM applicatie a WHERE a.ID_applicatie = d.ID_applicatie 
                      FOR XML PATH ('Application'), Type )
           FROM [database] d 
           WHERE d.instance_id = i.instance_id 
           FOR XML PATH ('Database'), Type 
           ) AS [Databases]
FROM [dbo].[instances] i FOR XML PATH('Instance'), root('Instances'), Elements

XML Output:

<instances>
  <instance>
    <name>SERVER1\INSTANCE</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.8888.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database1</name>
        <recoverymodel>SIMPLE</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application1</name>
          <authtype>SQL Server</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
  <instance>
    <name>SERVER2</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.9999.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database2</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
      <database>
        <name>Database3</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Testing</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
</instances><instances>
</instances>

XML Xpath Basics

/ XML /

Expressions

/rootelement/childelement //elementname
Return all elements named 'elementname' 

//elementname/childelement[1] 
Get only 1st childelement with name childelelement

. 
Current node

..
Parrent element

Functions

name()
Name of the current node

comment()
Display comments

text()
Display CDATA text

document('/path/to/file.xml')/rootelement/childelement 
Set pointer to external file

string-length(element/childelement)
Get string length

concat(element/childelement1, ' ', element/childelement2)
Concatenate strings

contains(elemen/childelement, 'substring')
String contains substring

starts-with(), normalize-space(), substring-before(), substring-after(), count() not(), true(), false(), ...

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>