Categories
MSSQL Server

MSSQL Select with XML Output

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>
Categories
XML

XML Xpath Basics

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

XML Transformation Example

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>

 

Categories
XML

XML Schema Example

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>