Databases
StackOverflow2013
Postsdbo
POgroup by multiple attributes from xml with xslt
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POgroup by multiple attributes from xml with xslt
    primarykey
    data
    text
    <p>I have the following xml</p> <pre><code>&lt;smses&gt; &lt;sms address="87654321" type="1" body="Some text" readable_date="3/09/2011 2:16:52 PM" contact_name="Person1" /&gt; &lt;sms address="87654321" type="2" body="Some text" readable_date="3/09/2011 2:36:41 PM" contact_name="Person1" /&gt; &lt;sms address="87654321" type="1" body="Some text" readable_date="3/09/2011 2:16:52 PM" contact_name="Person1" /&gt; &lt;sms address="123" type="2" body="Some text" readable_date="3/09/2011 10:56:24 AM" contact_name="Person2" /&gt; &lt;sms address="123" type="1" body="Some text" readable_date="3/09/2011 10:57:52 AM" contact_name="Person2" /&gt; &lt;sms address="123" type="2" body="Some text" readable_date="3/09/2011 10:56:24 AM" contact_name="Person2" /&gt; &lt;sms address="12345678" type="1" body="Some text" readable_date="3/09/2011 11:21:16 AM" contact_name="Person3" /&gt; &lt;sms address="12345678" type="2" body="Some text" readable_date="3/09/2011 11:37:21 AM" contact_name="Person3" /&gt; &lt;sms address="12345" type="2" body="Some text" readable_date="28/01/2011 7:24:50 PM" contact_name="(Unknown)" /&gt; &lt;sms address="233" type="1" body="Some text" readable_date="30/12/2010 1:13:41 PM" contact_name="(Unknown)" /&gt; &lt;/smses&gt; </code></pre> <p>I am trying to get an ouput like this (e.g. xml)</p> <pre><code>&lt;sms contact_name="person1"&gt; &lt;message type="1"&gt;{@body}&lt;/message&gt; &lt;message type="2"&gt;{@body}&lt;/message&gt; &lt;message type="1"&gt;{@body}&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="person2"&gt; &lt;message type="2"&gt;{@body}&lt;/message&gt; &lt;message type="1"&gt;{@body}&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="person3"&gt; &lt;message type="2"&gt;{@body}&lt;/message&gt; &lt;message type="1"&gt;{@body}&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="2"&gt;{@body}&lt;/message&gt; &lt;message type="1"&gt;{@body}&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="2"&gt;{@body}&lt;/message&gt; &lt;/sms&gt; </code></pre> <p>e.g. html</p> <pre><code>&lt;div&gt; &lt;h1&gt;Person: @contact_name (@address)&lt;/h1&gt; &lt;p&gt;message @type: @body&lt;/p&gt; &lt;/div&gt; </code></pre> <p>I have managed to do this with the following XSLT code (please excuse the code below does not reflect the html entirely, the output is the desired result!)</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output method="xml" indent="yes" /&gt; &lt;xsl:key name="txt" match="sms" use="@contact_name" /&gt; &lt;xsl:template match="smses"&gt; &lt;xsl:apply-templates select="sms[generate-id(.)=generate-id(key('txt', @contact_name)[1])]"&gt; &lt;xsl:sort select="@address" order="ascending" /&gt; &lt;/xsl:apply-templates&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms"&gt; &lt;h4&gt;&lt;xsl:value-of select="@contact_name" /&gt;&lt;/h4&gt; &lt;xsl:for-each select="key('txt', @contact_name)"&gt; &lt;br /&gt; &lt;xsl:value-of select="@body" /&gt; &lt;/xsl:for-each&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p>The problem I have is, or rather the question I'm asking. I have a sms element with a <code>@contact_name</code> attribute that is "(unknown)" but the <code>@address</code> is unique between both elements, i.e. they should not be grouped together, because the sms message came from a different number/person (even though the contact name is the same, its irrelevant). Should I be trying to reorder/change the XML data or is there a way to get XSLT to recognise the group for unknown should check if the <code>@address</code> is different if the <code>@contact_name</code> is the same.</p> <p>Edit:</p> <p>I failed to mention (or rather forgot) that while there are some sms messages with same <code>@contact_name</code> and unique <code>@address</code> there is also cases where some of the <code>@address</code> fields have slight discrepancy where they don't have the country code in front of the number, e.g. </p> <pre><code>&lt;sms contact_name="jared" address="12345" /&gt; &lt;sms contact_name="jared" address="+64112345" /&gt; </code></pre> <p>But they are meant to be grouped because they <em>are</em> from the same person/number.</p> <p>Edit:</p> <p>In my situation there would only be discrepancies of having 3 character (e.g. +64) country code plus 2 digit network code (e.g. 21). Basically the outcome should be, if <code>@contact_name</code> = same and <code>@address</code> is completely different i.e.</p> <pre><code> &lt;sms contact_name="jared" address="12345" /&gt; &lt;sms contact_name="jared" address="5433467" /&gt; </code></pre> <p>then they should be seperate elements, as they are from different people/number(s).</p> <p>if <code>@contact_name</code> = same and <code>@address</code> is different only by country and network codes i.e.</p> <pre><code> &lt;sms contact_name="jared" address="02112345" /&gt; &lt;sms contact_name="jared" address="+642112345" /&gt; </code></pre> <p>then they should be grouped as they are from the same person/number</p> <p>Edit:</p> <p>country codes: +64 (3 characters)</p> <p>network codes: 021 (3 characters, usually last character changes depending on network)</p> <p>Numbers (<code>@address</code>) get saved per <code>&lt;sms&gt;</code> either as +64-21-12345 (excluding dashes) or 021-12345(excluding dash).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJared
    1. USJared
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGood question, +1. Now you will be able to learn and apply Muenchian grouping using composite keys.
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. USDimitre Novatchev
    2. CO@_Jared: You need to explain (better by editing the question) the rules for prefixing with country code: Isit 2digits only or three digits, or varying number of digits? In case it is the latter, then the solution should have been provided with a list of all possible country codes.
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. USDimitre Novatchev
    3. CO@_Dimitre - Apologies, hope I've made it more clear now. I was so close to getting this working on my own until I hit this barrier. Much appreciate your help!
      singulars
      1. POgroup by multiple attributes from xml with xslt
      1. USJared
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload