Tim van Kooten Niekerk : DEVOPS

MSSQL Select with XML Output

2013-07-05 / MSSQL Server / Tim van Kooten Niekerk

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

2013-07-05 / XML / Tim van Kooten Niekerk

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(), ...

Functions XML Xpath

XML Transformation Example

2013-07-03 / XML / Tim van Kooten Niekerk

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>

Transform XML Xpath XSLT

GNU/Linux RegExp basics

2013-07-03 / GNU/Linux / Tim van Kooten Niekerk

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)

Expression RegExp Regular

XML Schema Example

2013-07-03 / XML / Tim van Kooten Niekerk

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>

ComplexType enumeration Namespace Restriction SimpleType XML XSD

GNU/Linux WGET Auth

2013-06-19 / GNU/Linux / Tim van Kooten Niekerk

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

Auth Chain Session SSL Wget

PowerShell Unzip Function

2013-04-08 / PowerShell Scripting / Tim van Kooten Niekerk

A small PowerShell function for unzipping a file.

function fUnzip([string]$sZIPFileName, [string]$sExtractToDir) {
  $oShell = new-object -com shell.application
  $oZIPFile = $oShell.namespace("$sZIPFileName")
  $oExtractTo = $oShell.namespace("$sExtractToDir")
  $oExtractTo.Copyhere($oZIPFile.items())
}

File Function Unzip

GNU/Linux STUNNEL Server config

2013-03-22 / GNU/Linux / Tim van Kooten Niekerk

Server STUNNEL config…

;debug = 7
;output = stunnel.log
cert = HU-TEST-SERVER.pem
verify = 3
CAfile = HU-TEST-KEYSTORE.pem
[test]
accept = 7080
connect = fq.host.name:7080

Network SSL Stunnel

PowerShell TCP Socket Function

2013-03-18 / PowerShell Scripting / Tim van Kooten Niekerk

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)

Function GET HTTP Network TCP Telnet

MSSQL Table Switch IN/OUT examples

2013-03-15 / MSSQL Server / Tim van Kooten Niekerk

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)

Alter Partition Switch Table