Xpath

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

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>