XML

PowerShell Validate XML Message

/ PowerShell Scripting /

Quick (raw) guide to validate a XML message using Powershell.

# Declare schemas opbject…
$schemas = New-Object System.Xml.Schema.XmlSchemaSet

# Read schema from file…
$schemaItem = Get-Item File.xsd
$fileStream = $schemaItem.OpenRead()
$xsd = [Xml.Schema.XmlSchema]::Read($fileStream, $null)
$fileStream.Close()

# Add XSD to schemas & compile…
$schemas.Add($xsd)
$schemas.Compile()

# Read & validate XML…
[xml]$xml = Get-Content .\File.xml
$xml.Schemas = $schemas
$xml.Validate($null)

Powershell command to generate a new GUID.

[guid]::NewGuid().Guid

MSSQL Query Value From XML Datatype

/ MSSQL Server /

Query some values from a XML datatype result…

SELECT [datumtijd]
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/"; 
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:ID'
                               ).value('.', 'nvarchar(100)')) AS ID
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:Name'
                               ).value('.', 'nvarchar(100)')) AS Name

      --,[body]
FROM [DATABASE].[dbo].[Logtable] 
WHERE [kolom1] = 'waarde' 
ORDER BY datumtijd DESC

PowerShell Webservices Probe

/ PowerShell Scripting /

The following script reads URL’s from an array to check if specific web services are online. When the script finds that one or more services are offline it sends an e-mail message (HTML). A TEXT version of the message is created in the Windows EventLog.

Het script uses custum functions: fnSendMail en fnWriteEventLog om berichten af te handelen.

# Global config settings...
[string]$sSMTPServer1 = "smtp.example.org"
[string]$sSubject1 = "EXAMPLE:WebService probes..."
[string]$sToAddress1 = "test@example.org"
[string]$sEventLogSource = "EVENTLOGSOURCE"
[switch]$swAlarm = $false
[array]$aWebServices1 = @(
   "https://example.org/webService?WSDL",
   "https://example.org/webService?WSDL",
)

# Functions and procedures...
function fnProbeWebSvc() {
  param (
    [string]$sWebSvcWSDL
  )
  $oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL
  if ($oWebSvc1) {
    [switch]$swWebSvcIsAlive = $true
  } else {
    [switch]$swWebSvcIsAlive = $false
  }  
  return $swWebSvcIsAlive
}

# Start Main script...
# Create array for results...
[array]$aWebServiceState = @()

# Fill table from array...
foreach ($row1_aWebServices1 in $aWebServices1) {
  $oWebServiceState = New-Object PSObject -Property @{
    'WebService' = $row1_aWebServices1
    'IsAlive' = (fnProbeWebSvc -sWebSvcWSDL $row1_aWebServices1) }
  # FLip Alarm status if fnProbeWebSvc return value is false...  
  if ($oWebServiceState.IsAlive -eq $false) {[switch]$swAlarm = $true}
  $aWebServiceState += $oWebServiceState
}

# Build HTML and TEXT body w. array data if alarm status is set to true...
if ($swAlarm -eq $true) {
  
  # Body header HTML...
  $sbBody1_HTML = New-Object System.Text.StringBuilder
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")
  [void]$sbBody1_HTML.Append("<table><tbody><tr bgcolor='grey'><th>WebService</th><th>IsAlive</th></tr>`r`n")

  # Body header TEXT...
  $sbBody1_TEXT = New-Object System.Text.StringBuilder
  [void]$sbBody1_TEXT.Append("Webservice(s) offline. Contact your administrators.`r`n`r`n")

  # Build table rows from array...
  foreach ($row1_aWebServiceState in $aWebServiceState) {
    # Build table rows HTML...
    [void]$sbBody1_HTML.Append("<tr bgcolor='lightblue'><td>")
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.WebService)
    # Row Coloring...
    if ($row1_aWebServiceState.IsAlive -eq $true) {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightgreen'>")
    } else {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightpink'>")
    }
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.IsAlive)
    [void]$sbBody1_HTML.Append("</td></tr>")

    # Build table rows TEXT (only show failed services...
      if ($row1_aWebServiceState.IsAlive -eq $false) {
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.WebService)
        [void]$sbBody1_TEXT.Append(": ")
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.IsAlive)
        [void]$sbBody1_TEXT.Append("`r`n")
      }
  }

  # Mail body footer...
  [void]$sbBody1_HTML.Append("</tbody></table>`r`n")
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")

  # Send HTML body in mail to admins...
  [string]$sBody1 = $sbBody1_HTML.ToString()
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sBody1 -swIsBodyHTML

  # Send text body to eventlog...
  fnWriteEventLog -sNode "NODENAME" -sMessage $sbBody1_TEXT.ToString() 
}

PowerShell WebService Calls

/ PowerShell Scripting /

Performing a SOAP web service call using PowerShell. The methods can be requested by means of the command: $oWebSvc1 | Get-Member.

#WebService values...
[string]$sWebSvcWSDL = "https://host.domain.fqdn/Webservice1?wsdl"
[string]$sPostValue1 = "Waarde"
[string]$sPostValue2 = $true

#Create webservice object...
$oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL

#POST values w. result in een object...
$oResult1 = $oWebSvc1.method1($sPostValue1, $sPostValue2)

#POST values w. result to RAW XML...
[xml]$xResult = $oWebSvc1.method1($sPostValue1, $sPostValues2) | ConvertTo-Xml
$xResult.get_OuterXML() | Out-File C:\Pad\Naar\Bestand.xml

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>

 

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>