• community supported and free
• created and steered by Luca Passani, mobile genius apparent
• a great source to find out about the capabilities of thousands of mobile devices

You can check out the WURFL project here. There are other WURFL XSLT tools that can be used in conjunction (as a pre-transformation step) with my following contributions on this page. In this article I will provide an XSLT transformation that will create a denormalized, text-based data file that represents all of the data including preserving the original relationships. In a future article, Importing the WURFL Mobile Device XML into a SQL Database, I will provide SQL code for building a fast, run-time datamart from the mobile device capabilities.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" />
  <xsl:template match="/">
    <xsl:text>DeviceId~DeviceDepth~DeviceIsActual~DeviceUserAgent~</xsl:text>
    <xsl:text>DeviceParentId~GroupId~CapabilityDepth~CapabilityId~CapabilityValue&#xA;</xsl:text>
    <xsl:apply-templates select="wurfl/devices/device" />
  </xsl:template>
  <xsl:template match="device">
    <xsl:message>
      <xsl:value-of select="position()" />
      <xsl:text> - </xsl:text>
      <xsl:value-of select="@id" />
    </xsl:message>
    <xsl:call-template name="capabilities">
      <xsl:with-param name="device" select="." />
    </xsl:call-template>
  </xsl:template>
  <xsl:template name="capabilities">
    <xsl:param name="device" select="/.." />
    <xsl:param name="current" select="$device" />
    <xsl:param name="depth" select="0" />
    <xsl:param name="found" select="'~'" />
    <xsl:variable name="id" select="string($device/@id)"  />
    <xsl:variable name="parentId" select="string($device/@fall_back)" />
    <xsl:variable name="capabilities"
      select="$device/group/capability[not(contains($found,concat('~',@name,'~')))]" />
    <xsl:apply-templates select="$capabilities">
      <xsl:with-param name="deviceId" select="string($current/@id)" />
      <xsl:with-param name="deviceIsActual" select="boolean($current/@actual_device_root)" />
      <xsl:with-param name="deviceUserAgent" select="$current/@user_agent" />
      <xsl:with-param name="deviceParentId" select="string($current/@fall_back)" />
      <xsl:with-param name="depth" select="$depth" />
    </xsl:apply-templates>
    <xsl:if test="$parentId!='root'">
      <xsl:variable name="capabilitiesText">
        <xsl:for-each select="$capabilities">
          <xsl:value-of select="@name" />
          <xsl:text>~</xsl:text>
        </xsl:for-each>
      </xsl:variable>
      <xsl:variable name="parentDevice" select="../device[@id=$parentId]" />
      <xsl:if test="$parentDevice">
      <xsl:call-template name="capabilities">
        <xsl:with-param name="device" select="$parentDevice" />
        <xsl:with-param name="current" select="$current" />
        <xsl:with-param name="depth" select="$depth+1" />
        <xsl:with-param name="found" select="concat($found,string($capabilitiesText))" />
      </xsl:call-template>
      </xsl:if>
    </xsl:if>
  </xsl:template>
  <xsl:template match="capability">
    <xsl:param name="deviceId" />
    <xsl:param name="deviceIsActual" />
    <xsl:param name="deviceUserAgent" />
    <xsl:param name="deviceParentId" />
    <xsl:param name="depth" />
    <xsl:value-of select="$deviceId" />
    <xsl:text>~</xsl:text>
    <xsl:choose>
      <xsl:when test="$deviceIsActual">
        <xsl:text>1</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>0</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:text>~</xsl:text>
    <xsl:value-of select="$deviceUserAgent" />
    <xsl:text>~</xsl:text>
    <xsl:value-of select="$deviceParentId" />
    <xsl:text>~</xsl:text>
    <xsl:value-of select="../@id" />
    <xsl:text>~</xsl:text>
    <xsl:value-of select="$depth" />
    <xsl:text>~</xsl:text>
    <xsl:value-of select="@name" />
    <xsl:text>~</xsl:text>
    <xsl:value-of select="@value" />
    <xsl:text>&#xA;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

The resulting data file consists of one row containing column names and then one row for each capabaility, for each device. The fields are delimited by tilde ('~', ~) and the rows by new line ('n', 
). The output file will be very large so you may need a tool like msxsl.exe to complete the conversion (Visual Studio 2005 runs out of memory). The most recent version of wurfl.xml is about 7MB and the data file for it is almost 900MB. Why so big? This solution is optimized for read-only access via very simple database queries without consideration of the data's size.

Once your file has been generated, you can use this code to import it into a table.

CREATE TABLE [DeviceCapability]
    ([DeviceId] VARCHAR(100) NOT NULL, [DeviceIsActual] BIT NOT NULL,
    [DeviceUserAgent] VARCHAR(200) NOT NULL, [DeviceParentId] VARCHAR(100) NOT NULL,
    [GroupId] VARCHAR(100) NOT NULL, [CapabilityDepth] INTEGER NOT NULL,
    [CapabilityId] VARCHAR(100) NOT NULL, [CapabilityValue] VARCHAR(200) NOT NULL);

BULK INSERT [DeviceCapability] FROM '...wurfl.dat'
WITH (FIRSTROW = 2, FIELDTERMINATOR = '~', ROWTERMINATOR = 'n');

CREATE TABLE DeviceCapability
    (DeviceId VARCHAR(100) NOT NULL, DeviceIsActual BIT NOT NULL,
    DeviceUserAgent VARCHAR(200) NOT NULL, DeviceParentId VARCHAR(100) NOT NULL,
    GroupId VARCHAR(100) NOT NULL, CapabilityDepth INTEGER NOT NULL,
    CapabilityId VARCHAR(100) NOT NULL, CapabilityValue VARCHAR(200) NOT NULL);

LOAD DATA INFILE '...wurfl.dat' INTO TABLE DeviceCapability
FIELDS TERMINATED BY '~' LINES TERMINATED BY 'n';