Originally posted FRIDAY, FEBRUARY 17. 2012
This is a repost of an entry from my previous, but now defunct blog. The original URL was http://pascaliburnus.seanbdurkin.id.au/index.php?/archives/17-A-Generalised-and-Comprehensive-Solution-to-CSV-to-XML-and-XML-to-CSV-Transformations.html, and the entry can still be seen in its original rendering via the Way Back Machine

Abstract:This entry provides a generalised and comprehensive solution to the conversion of Comma Separated Values (CSV) file to XML. CSV is a very common file format for tabular data. It is clear from the frequency of related questions on StackOverflow, that there is a strong public demand for a generalised solution to CSV to XML and XML to CSV conversion. I believe that this entry marks the first published solution to the said transformations, which defines the model of CSV that it applies to, is generalised to work with all conformant CSV, in the XML domain defines the schema of the XML representation of the CSV file, and is error free. A feature of this solution is that the XML representation is constrained by an XML Schema. This is as opposed to other possible transformations which might derive the XML node names from the CSV header values, which in a generalised sense are unknown.

A quick example

Colour," Make", model
Lime ,"GMH","23 "
Black,Ford,14

<?xml version="1.0" encoding="UTF-8"?>
<xcsv:comma-separated-single-line-values
  xmlns:xcsv="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
  xcsv-version="1.0">
   <xcsv:notice xml:lang="en">The xcsv format was developed by Sean B. Durkin&#133;www.seanbdurkin.id.au</xcsv:notice>
   <xcsv:row>
      <xcsv:value>Colour</xcsv:value>
      <xcsv:value> Make</xcsv:value>
      <xcsv:value> model</xcsv:value>
   </xcsv:row>
   <xcsv:row>
      <xcsv:value>Lime </xcsv:value>
      <xcsv:value>GMH</xcsv:value>
      <xcsv:value>23 </xcsv:value>
   </xcsv:row>
   <xcsv:row>
      <xcsv:value>Black</xcsv:value>
      <xcsv:value>Ford</xcsv:value>
      <xcsv:value>14</xcsv:value>
   </xcsv:row>
</xcsv:comma-separated-single-line-values>

Statement of copying permission

Excluding any code written by Andrew Welch, readers are permitted to copy the style-sheets and schema listed in this post, without let or hinderance.

What is CSV?

For the purposes of defining this pair of transformations, I will define the CSV format. My definition is based on RFC 4180 October 2005 “Common Format and MIME Type for CSV Files” but with some minor changes to suite the needs of XSLT based transformations.

• The CSV format is a text stream encoded in either UTF-8 or UTF-16. If encoded in UTF-16, it must be prefixed by a BOM. A BOM is optional for UTF-8. No other encoding is valid. In any case, the csv file must be correctly readable by the XSLT 2.0 function unparsed-text()
• A CSV stream is a contiguous sequence of text lines, also known as rows.
• Lines are separated by line terminators. The last line can be terminated by either a line terminator or the end of the stream. If the last sequence of characters is a line terminator, then this line terminator does not signify the separtion of the previous line and a following empty line, but rather it follows and terminates the last line. Refer to sections 2.1 and 2.2 of RFC 4180 for examples.
• Valid line terminators are:

• The sequence U+00000D (CR) U+00000A (LF) or
• the character U+00000A (LF).

Other line terminators which might exist in some file-systems or recognised as line terminators by XML 1.1, are not recognised. Counter-examples include:

• The sequence U+00000A (LF) U+00000D (CR),
• the character U+00000D (CR),
• U+000085 (NEL) or
• U+002028 (LS)
• The first line is the header row. Subsequent rows are data rows. The values of the header row are respectively positioned headings for the values of the data rows. In contrast to RFC 4180, there is always exactly one header row.
• Headings are not required to be unique, but they cannot be empty or consist of entirely of white-space. For this purpose, white-space is defined as per XML 1.1 .
• The heading row has at least one heading (cell in the header row).
• Each row is a list of cells. Cells are encoded string values, and are separated by the comma (,) character.
• There is at least one cell per row. If the line is empty of characters, it signifies a row with one cell, whose value is empty.
• Two output options are available for transforming to xml format: One encapsulated with node >xcsv:comma-separated-single-line-values/< and the other >xcsv:comma-separated-multi-line-values/<
• In the case of >xcsv:comma-separated-single-line-values/< output, cells may not contain a line terminator (LF or the CR LF pattern), but may contain a CR not followed by an LF, or may contain NEL or LS. If content developers require multi-line cells, it is recommended that the LS character be used as a content-line separator within cells.
• Cells are either encoded as quoted values or unquoted values.
• An unquoted value represents the same value as its encoding string. An unquoted value may not contain either the comma (,) character or the quote (“) character.
• A quoted value represents the same value as its encoding string, except that it is delimited on both sides by quote (“) marks (which are part of the encoding, not the value), and any value-bearing quote marks are escaped in the representation as a double occurance of the quote character (“).
• White space, not as a line separator, is always significant and a part of the cell value.
• There is no requirement for the count of cells in any data row to match the count of cells in the header. In other words, ragged csv is permissible.

Specification of the schema of the XML representation of CSV

The XML schema for the XML representation of CSV is as follows. It can be referenced in binary form or downloaded as indicated in the xs:schema targetNamespace.

(Click the word Schema below to expand, if you need to read.)

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:xcsv="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
    elementFormDefault="qualified"
    targetNamespace="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
    version="1.0">

  <xs:import
      namespace="http://www.w3.org/XML/1998/namespace" 
      schemaLocation="xml.xsd"/>

  <xs:element name="comma-separated-single-line-values">
   <xs:annotation><xs:documentation xml:lang="en">
    This schema describes an XML representation of a subset of csv content.
    The format described by this schema, here-after referred to as "xcsv"
    is part of a generalised solution to the problem of converting
    general csv files into suitable XML, and the reverse transform.

    The restrictions on the csv content (in relation to
	xcsv:comma-separated-single-line-values) are:
      <strong> The csv file is encoded either in UTF-8 or UTF16. If UTF-16, a BOM
        is required.
      </strong> The cell values of the csv may not contain the CR or LF characters.
        Essentially, we are restricted to single-line values.

	A multi-line version follows.
	
    The xcsv format was developed by Sean B. Durkin&#x85;www.seanbdurkin.id.au
   </xs:documentation></xs:annotation>

   <xs:complexType>
    <xs:sequence>

     <xs:element ref="xcsv:notice" minOccurs="0" maxOccurs="1"/>
     <xs:element name="row" minOccurs="0" maxOccurs="unbounded">
      <xs:annotation><xs:documentation xml:lang="en">
       A row element represents a "row" or "line" in the csv file. Rows contain values.
       </xs:documentation>
       <xs:appinfo>
        <example>
         <csv-line>apple,"banana","red, white and blue","quote this("")"</csv-line>
         <row>
          <value>apple</value>
          <value>banana</value>
          <value>red, white and blue</value>
          <value>quote this(")</value>
         </row>
        </example>
       </xs:appinfo>
      </xs:annotation> 
        <xs:choice minOccurs="1" maxOccurs="unbounded">
       <xs:annotation><xs:documentation xml:lang="en">
         Empty rows are not possible in csv. We must have at least one value or one error.
       </xs:documentation></xs:annotation>
       <xs:element name="value">
        <xs:annotation><xs:documentation xml:lang="en">
         A value element represents a decoded (model) csv "value" or "cell".
         If the encoded value in the lexical csv was of a quoted form, then
         the element content here is the decoded or model form. In other words,
         the delimiting double-quote marks are striped out and the internal
         escaped double-quotes are de-escaped.
        </xs:documentation></xs:annotation>
        <xs:simpleType>
         <xs:restriction base="xs:string">
          <xs:pattern value="[^\n]*"/>
          <xs:whiteSpace value="preserve"/>
          <xs:annotation><xs:documentation xml:lang="en">
           Cell values must fit this pattern because of the single-line restriction
           that we placed on the csv values.
          </xs:documentation></xs:annotation>
         </xs:restriction>
        </xs:simpleType>
       </xs:element>
       <xs:group ref="xcsv:errorGroup">
        <xs:annotation><xs:documentation xml:lang="en">
          An error can be recorded here as a child of row, if there was an encoding
          error in the csv for that row.
        </xs:documentation></xs:annotation>
       </xs:group>
        </xs:choice>
     </xs:element>

     <xs:group ref="xcsv:errorGroup">
      <xs:annotation><xs:documentation xml:lang="en">
       An error can be recorded here as a child of the comma-separated-values element,
       if there was an i/o error in the transformational process. For example:
        CSV file not found.
      </xs:documentation></xs:annotation>
     </xs:group>

    </xs:sequence>

    <xs:attribute name="xcsv-version" type="xs:decimal"
        fixed="1.0" use="required"/>
   </xs:complexType>
  </xs:element>

  <xs:element name="comma-separated-multiline-values">
   <xs:annotation><xs:documentation xml:lang="en">
    Similar to xcsv:comma-separated-multi-line-values but allows multi-line values.
   </xs:documentation></xs:annotation>

   <xs:complexType>
    <xs:sequence>

     <xs:element ref="xcsv:notice" minOccurs="0" maxOccurs="1"/>
     <xs:element name="row" minOccurs="0" maxOccurs="unbounded">
       <xs:choice minOccurs="1" maxOccurs="unbounded">
       <xs:element name="value">
        <xs:simpleType>
         <xs:restriction base="xs:string">
          <xs:whiteSpace value="preserve"/>
         </xs:restriction>
        </xs:simpleType>
       </xs:element>
       <xs:group ref="xcsv:errorGroup">
       </xs:group>
        </xs:choice>
     </xs:element>

     <xs:group ref="xcsv:errorGroup">
     </xs:group>

    </xs:sequence>

    <xs:attribute name="xcsv-version" type="xs:decimal"
        fixed="1.0" use="required"/>
   </xs:complexType>
  </xs:element>

 <xs:element name="notice" type="xcsv:notice-en" />
      <xs:annotation><xs:documentation xml:lang="en">
       This is an optional element below comma-separated-single-line-values or
        comma-separated-multiline-values that looks like the example.
       </xs:documentation>
      <xs:appinfo>
       <example>
        <notice xml:lang="en">The xcsv format was developed by Sean B. Durkin&#x85;www.seanbdurkin.id.au</notice>
       </example>
      </xs:appinfo></xs:annotation>
      <xs:complexType name="notice-en">
        <xs:simpleContent>
          <xs:extension base="xcsv:notice-content-en">
           <xs:attribute ref="xml:lang" use="required" fixed="en" />
          </xs:extension>
        </xs:simpleContent>
      </xs:complexType>
      <xs:simpleType name="notice-content-en">
       <xs:restriction base="xs:string">
         <xs:enumeration value="The xcsv format was developed by Sean B. Durkin&#x85;www.seanbdurkin.id.au"/>
       </xs:restriction>
      </xs:simpleType>
 <xs:element />

   <xs:group name="errorGroup">
      <xs:annotation><xs:documentation xml:lang="en">
       This is an error node/message in one or more languages.
      </xs:documentation>
      <xs:appinfo>
       <example>
        <error error-code="2">
         <message xml:lang="en">Quoted value not terminated.</message>
         <message xml:lang="ru">процитированная строка не закрыта.</message>
         <error-data>"</error-data>
        </error>
       </example> 
       <example>
        <error error-code="3">
         <message xml:lang="en">Quoted value incorrectly terminated.</message>
         <message xml:lang="ru">процитированная строка закрыта неправильно.</message>
        </error>
       </example>
      </xs:appinfo> 
      </xs:annotation>
   <xs:element name="error">
    <xs:element name="message" minOccurs="1" maxOccurs="unbounded" type="xcsv:string-with-lang" />
      <xs:annotation><xs:documentation xml:lang="en">
       Although there can be multiple messages, there should only be at most one per language.
      </xs:documentation></xs:annotation>
    <xs:element name="error-data" minOccurs="0" maxOccurs="1" >
     <xs:simpleContent>
      <xs:restriction base="xs:string">
       <xs:whiteSpace value="preserve"/>
      </xs:restriction>
     </xs:simpleContent>
    </xs:element>
    <xs:attribute name="error-code" type="xs:positiveInteger" default="1" />
      <xs:annotation><xs:documentation xml:lang="en">
       Each different kind of error should be associated with a unique error code.
       A map for the error codes is outside the scope of this schema, except to say the following:
         <strong> one (1) means a general or uncategorised error. (Try to avoid this!)
      </xs:documentation></xs:annotation>
   </xs:element>
  </xs:group>

  <xs:complexType name="string-with-lang">
      <xs:annotation><xs:documentation xml:lang="en">
       This is an element with text content in some language as indicated
       by the xml:lang attribute.
      </xs:documentation></xs:annotation>
   <xs:simpleContent>
    <xs:extension base="xs:string">
     <xs:attribute ref="xml:lang" use="required" default="en" />
    </xs:extension>
   </xs:simpleContent>
  </xs:complexType>


</xs:schema> 

Rules for Transformation

When transforming from CSV to XML:

• The transformation should only output UTF-8 encoded XML.
• The transformation should check for validity of the CSV. If invalid, an element should be written directly underneath the element. Refer to the schema.
• The schema does not require that the xcsv:comma-separated-single-line-values or xcsv:comma-separated-multiline-values elements be the document element, but if transforming one CSV file to one XML file, this should be made the case.

XSLT solutions

Here is a style-sheet to convert from csv (single line values case only) to xcsv format (output type is root node). A significant part of this style-sheet is derived from or copied from Andrew Welch’s solution. A binary copy is located at ‘http://www.seanbdurkin.id.au/xslt/csv-to-xml.xslt’. The style-sheet takes two parameters: url-of-csv and lang. url-of-csv is the filename or reference to the csv file to be converted. ‘lang’, a parameter defaulting to ‘en’ specifies the human language of error messages. If non-english language is chosen, the transform needs to be supported by a language file (param ‘url-of-messages’) with the localised error messages.

I plan to make a style sheet for the multi-line csv case in a future post, as well as the inverse transforms (xcsv to csv). If you are interested, either watch this blog, or take it as an exercise for the reader to design these style-sheets. If you design such style-sheets, let me know in the comments for this post.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE constants[
  <!ENTITY notice "The xcsv format was developed by Sean B. Durkin&#x85;www.seanbdurkin.id.au">
  ]>  
<xsl:stylesheet 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:fn="http://www.w3.org/2005/xpath-functions" 
  xmlns:local="http://www.seanbdurkin.id.au/xslt/csv-to-xml.xslt" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:xcsv="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
  version="2.0"
  exclude-result-prefixes="xsl xs fn local">
<xsl:output indent="yes" encoding="UTF-8" method="xml"/>
<xsl:import-schema schema-location="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
                   use-when="system-property('xsl:is-schema-aware')='yes'" />
				   
<!-- Read Me ! -->
<!-- ************************************************************************</strong> -->
<!-- A significant part of this style-sheet is derived from or copied from     --> 
<!-- Andrew Welch's solution. Refer: http://andrewjwelch.com/code/xslt/csv/csv-to-xml_v2.html -->
<!-- Modifications have been made by me (Sean B. Durkin) in order to meet the design         -->
<!-- goals as described here:                                                                -->
<!-- http://pascaliburnus.seanbdurkin.id.au/index.php?/archives/17-A-Generalised-and-Compreh -->
<!--ensive-Solution-to-CSV-to-XML-and-XML-to-CSV-Transformations.html				         -->

<!-- Stylesheet parameters -->
<!-- ************************************************************************<strong> -->
<xsl:param name="url-of-csv" as="xs:string" />
<xsl:param name="lang" as="xs:string" select="'en'" />
<xsl:param name="url-of-messages" as="xs:string" />

<!-- Configurable constants -->
<!-- ************************************************************************</strong> -->
  <xsl:variable name="quote" as="xs:string">"</xsl:variable>
  <xsl:variable name="error-messages-i18n">
   <xcsv:error error-code="1">
    <xcsv:message xml:lang="en">Uncategorised error.</xcsv:message>
   </xcsv:error>
   <xcsv:error error-code="2">
    <xcsv:message xml:lang="en">Quoted value not terminated.</xcsv:message>
   </xcsv:error>
   <xcsv:error error-code="3">
    <xcsv:message xml:lang="en">Quoted value incorrectly terminated.</xcsv:message>
   </xcsv:error>
   <xcsv:error error-code="5">
    <xcsv:message xml:lang="en">Could not open CSV resource.</xcsv:message>
   </xcsv:error>
  </xsl:variable> 
  
<!-- Non-configurable constants -->
<!-- ************************************************************************<strong> -->
 <xsl:variable name="error-messages">
   <xsl:apply-templates select="$error-messages-i18n" mode="messages" />
  </xsl:variable>
    
<xsl:template match="@*|node()" mode="messages" >
 <xsl:copy>
  <xsl:apply-templates select="@*|node()" mode="messages" />
 </xsl:copy>
</xsl:template>

<xsl:template match="xcsv:message[
      not(@xml:lang=$lang) and
     (not(@xml:lang='en') or ../xcsv:message[@xml:lang=$lang])]" mode="messages" />
	 


<xsl:function name="local:unparsed-text-lines" as="xs:string+">
 <xsl:param name="href" as="xs:string" />
 <xsl:sequence use-when="function-available('unparsed-text-lines')" select="fn:unparsed-text-lines($href)" />
 <xsl:sequence use-when="not(function-available('unparsed-text-lines'))" select="tokenize(unparsed-text($href), '\r\n|\r|\n')[not(position()=last() and .='')]" />
</xsl:function>

<xsl:function name="local:error-node" as="node()">
 <xsl:param name="error-code" as="xs:integer" />
 <xsl:param name="data" as="xs:string" />
 <xcsv:error error-code="{$error-code}">
  <xcsv:message
    xml:lang="{$error-messages/xcsv:error[@error-code=$error-code]/xcsv:message/@xml:lang}">
    <xsl:value-of select="$error-messages/xcsv:error[@error-code=$error-code]/xcsv:message"/>
  </xcsv:message>
  <xcsv:error-data><xsl:value-of select="$data"/></xcsv:error-data>
 </xcsv:error>
</xsl:function>

<xsl:function name="local:csv-to-xml" as="node()+">
 <xsl:param name="href" as="xs:string" />
 <xcsv:comma-separated-single-line-values xcsv-version="1.0">
  <xcsv:notice xml:lang="en">&notice;</xcsv:notice>
  <xsl:choose>
   <xsl:when test="fn:unparsed-text-available($href)">
    <xsl:for-each select="local:unparsed-text-lines($href)">
	 <xcsv:row>
     <xsl:analyze-string select="fn:concat(., ',')" regex='(("[^"]*")+|[^,"]*),'>
      <xsl:matching-substring>
	   <xcsv:value>
	    <xsl:choose>
         <xsl:when test="fn:starts-with( fn:regex-group(1), $quote)">
          <xsl:value-of select='fn:replace(fn:regex-group(1), "^""|""$|("")""", "$1" )' />
	     </xsl:when>
	     <xsl:otherwise>
          <xsl:value-of select='regex-group(1)' />
	     </xsl:otherwise>
	    </xsl:choose>
	   </xcsv:value>
      </xsl:matching-substring>
      <xsl:non-matching-substring>
       <xsl:copy-of select="local:error-node(3,.)"/>
       <!-- Quoted value incorrectly terminated. -->
      </xsl:non-matching-substring>
     </xsl:analyze-string>
	 </xcsv:row>
    </xsl:for-each>
   </xsl:when>
   <xsl:otherwise>
    <xsl:copy-of select="local:error-node(5,$href)"/>
    <!-- Could not open CSV resource. -->
   </xsl:otherwise>
  </xsl:choose>
 </xcsv:comma-separated-single-line-values>
</xsl:function>

<xsl:template match="/" name="local:main">
 <xsl:copy-of select="local:csv-to-xml($url-of-csv)" />
</xsl:template>


</xsl:stylesheet>

Unit testing

Please refer to http://codereview.stackexchange.com/questions/10180/please-review-my-xml-schema-for-an-xml-representation-of-csv. The Use cases can be used for unit testing.

Case Study

1st Name,2nd Name
Sean B.,Durkin
"Chris ""Nemora""",Durkin

<?xml version="1.0" encoding="UTF-8"?>
<people>
   <person>
      <first-name>Sean B.</first-name>
      <last-name>Durkin</last-name>
   </person>
   <person>
      <first-name>Chris "Nemora"</first-name>
      <last-name>Durkin</last-name>
   </person>
</people>

Assumptions

We will assume:

• The csv file is encoded in utf-8 without BOM.
• The csv file uses CR.LF for its end of line marker.
• The csv file may or may not have an CR.LF at the end of the last line (right after “Durkin”).
• The aforementioned xcsv style-sheet has been set-up as a standard library, whose location is known by your XSLT engine (probably via your configured OASIS catalog for the XSLT engine).
• The location of the csv file has been passed in to parameter ‘url-of-csv’

So the solution is …

<xsl:stylesheet 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:fn="http://www.w3.org/2005/xpath-functions" 
  xmlns:xcsvt="http://www.seanbdurkin.id.au/xslt/csv-to-xml.xslt" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:xcsv="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
  version="2.0"
  exclude-result-prefixes="xsl xs fn xcsvt xcsv">
<xsl:import href="csv-to-xml.xslt" />
<xsl:output indent="yes" encoding="UTF-8" method="xml"/>
<xsl:import-schema schema-location="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
                   use-when="system-property('xsl:is-schema-aware')='yes'" />
<xsl:param name="url-of-csv" as="xs:string" />

<xsl:import-schema schema-location="http://www.seanbdurkin.id.au/xslt/xcsv.xsd"
                   use-when="system-property('xsl:is-schema-aware')='yes'" />


<xsl:template match="/">
  <xsl:variable name="phase-1-result">
    <xsl:call-template name="xcsvt:main" /><!-- phase 1 -->
  </xsl:variable>
  <xsl:apply-templates select="$phase-1-result" mode="phase-2"/>
 <people>
   <xsl:apply-templates select="
     $phase-1-result/
	 xcsv:comma-separated-single-line-values/
	 (xcsv:row[position() ge 2])" mode="phase-2"/>
 </people>
</xsl:template>

<xsl:template match="@*|</strong>" mode="phase-2" />

<xsl:template match="xcsv:row" mode="phase-2">
 <person>
  <first-name><xsl:value-of select="xcsv:value[1]" /></first-name>
  <last-name> <xsl:value-of select="xcsv:value[2]" /></last-name>
 </person>
</xsl:template>

</xsl:stylesheet>				   

Let’s say you are required to keep a time-sheet for services performed, and for the most part, the hours of services performed corresponds to when you are logged into a specific Win7+ machine. The best way to keep a time-sheet is with pencil and paper, or a spread-sheet, or the corporate time-sheet keeping system. But what if there is a problem with your normal record-keeping system? Is there a way to leverage the windows event log without an enormous amount of pain?

Yes, there is! Now for the good oil …

Step One

Open the Event Viewer (%windir%\system32\eventvwr.msc /s)

Step Two

[aside]
caption = What does the query do?
alignment = right
collapse_state = collapsed
corners = round
hr_style = sbd-swish
bg_colour = #E0D584
width = 300px

This query selects events from

• the “Security” Log (the Path= part).
• It selects events from the named computer (the Computer= part);
• informational (as opposed to errors, warnings etc (Level=4 or Level=0 part);
• with a keyword “Audit Success” (the band part);
• not too old (TimeCreated… part); and
• either any kind of log-out (EventID’s 4634 or 4647);
• or a manual (as opposed to programmatic) log-in (EventId=4624 is log-in; LogonType=2 is the manual part).

[/aside]
Create a custom view (Action | Create Custom View...). Select the XML tab, and check the checkbox “Edit query manually”. Enter in the following query:

<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">*
	  [System[(Computer='E91W7CLI64') and (Level=4 or Level=0) and (band(Keywords,9007199254740992)) and TimeCreated[timediff(@SystemTime) &lt;= 9002000000]]]
	  [System[(EventID=4634 or EventID=4647)] or
	   (System[EventID=4624] and EventData/Data[@Name='LogonType']=2)]
	</Select>
  </Query>
</QueryList>

You will have to tweak two parameters in the above query, for your circumstances. The tweaks are:

1. Change Computer='E91W7CLI64' to your local machine name. (hint: right-click properties on your Computer icon to get the name)
2. Change the number part of the TimeCreated[timediff(@SystemTime) <= 9002000000 expression to how-ever far back you want to observe. This particular number equates to about 60 days. Adjust proportionally as required.

Name the filter as you please. I call mine “Logins”. This view observes successful manual log-ins and log-outs for the named computer for the specified time-back until now.

Step Three

Right-click on the filter and select Save All Events in Custom View As.... Select XML as the output format. The exported file now contains all your manual log-in session data.

Step Four

Transform the exported file with this XSLT script. I have tested this with the Saxon XSLT processor (Community edition).

<xsl:stylesheet version="2.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ev="http://schemas.microsoft.com/win/2004/08/events/event"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:fn="http://www.w3.org/2005/xpath-functions"
  xmlns:l="http://www.seanbdurkin.id.au"
  exclude-result-prefixes="xsl xs fn ev">
<xsl:output omit-xml-declaration="yes" encoding="utf-8" indent="yes"/>
<xsl:strip-space elements="*" />
      
<xsl:template match="Events">
 <l:sean-work-log>
  <xsl:variable name="sessions"> 
    <xsl:for-each-group select="ev:Event" group-by="ev:EventData/ev:Data[@Name='TargetLogonId']">
	  <xsl:if test="count(current-group()) eq 2">
	    <xsl:variable name="start"    as="xs:dateTime" select="xs:dateTime( current-group()[2]/ev:System/ev:TimeCreated/@SystemTime)" />
	    <xsl:variable name="end"      as="xs:dateTime" select="xs:dateTime( current-group()[1]/ev:System/ev:TimeCreated/@SystemTime)" />
	    <xsl:variable name="duration" as="xs:duration" select="$end - $start" />
	    <l:session start="{$start}" end="{$end}"
			   x-duration="{$duration}" 
			   day="{fn:format-date( xs:date( $start),'[FNn] [D] [MNn] [Y]')}"
			   duration="{fn:days-from-duration($duration) * 24 + fn:hours-from-duration($duration)} hours and {fn:minutes-from-duration($duration)} minutes" />
	  </xsl:if>
    </xsl:for-each-group>
  </xsl:variable>
 <l:work-days from="{fn:format-date( fn:min( for $d in $sessions/l:session/@start return xs:date( xs:dateTime($d))),'[FNn] [D] [MNn] [Y]')}"
              to="{  fn:format-date( fn:max( for $d in $sessions/l:session/@start return xs:date( xs:dateTime($d))),'[FNn] [D] [MNn] [Y]')}"> 
  <xsl:for-each-group select="$sessions/l:session" group-by="xs:date( xs:dateTime( @start))">
   <xsl:sort  select="xs:date( current-grouping-key())" order="descending" data-type="number" />
   <xsl:variable name="hours" select="fn:sum( for $x in current-group()/@x-duration return fn:minutes-from-duration( $x)) div 60" />
   <xsl:if test="fn:round($hours) ne 0">
     <l:work-day
       day="{fn:format-date( current-grouping-key(),'[FNn] [D] [MNn] [Y]')}"
       x-day="{current-grouping-key()}"
	   round-hours="{fn:round($hours)} hours" 
	   x-hours="{$hours}" />
   </xsl:if>	   
  </xsl:for-each-group>
 </l:work-days>  
 <l:log-sessions>
  <xsl:copy-of select="$sessions" />
 </l:log-sessions>
 </l:sean-work-log>
</xsl:template>
	  
</xsl:stylesheet>
        

Step Five

Read the output document and transcribe to your work-sheet. The output has two sections: A day-by=day summary and a list of log-in sessions. In the day-by-day summary, if the total for the day is less than half an hour, it will be filtered out. Hours for each day are rounded to the nearest hour. If you have different rounding rules, adjust the transform in step four as required.

In this post, I explore short-circuit boolean evaluation in XPath.

Let’s start with this reference task:

Let $seq be a possibly empty sequence of integer. The task is to compute a boolean which is true if the first item is the maximum integer and false otherwise. For this purpose, deem the case of an empty sequence NOT have the property that the first item is the maximum. Now there many possible solutions to this task, but for argument’s sake, let’s restrict our solutions to the form of …

index-of( $seq, max( $seq))[1]
    eq 1

… or something derived from this.

The problem with the using above as in in XPath 2.0 occurs with an edge case. If $seq is empty, this will return an error. So what we really want is this imaginary XPath 2.0 expression..

exists( $seq) short-circuit-and
              index-of( $seq, max( $seq))[1] eq 1

where ‘short-circuit-and’ is an imaginary XPath 2.0 operator which does short-circuit boolean evaluation from left to right. When XPath 1.0 compatibility mode is set to true, we can use the ‘and’ operator for this purpose. Sadly we cannot safely do so when the comp ability mode is set to false. In the default case of compatibility mode equals false, the above expression returns an implementation dependent result. For the edge case, some processors may return false and other processors may return error. In other words, XPath 2.0 does not implement short-circuit boolean evaluation.

Here is how we can achieve the effect of short-circuit boolean evaluation in XPath 2.0

if (exists( $seq))
 then index-of( $seq, max( $seq))[1] eq 1
 else false()

Here are the implementation patterns for short-circuit boolean evaluation in XPath 2.0 (and probably the future XPath 3.0).

Short-circuit AND

expression-a short-circuit-and expression-b 
  ==>  if (expression-a) then (expression-b) or false()

Short-circuit OR

expression-a short-circuit-or expression-b 
  ==>  if (expression-a) then true() else expression-b