Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p><strong>This transformation uses Muenchian grouping with composite keys</strong>:</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output omit-xml-declaration="yes" indent="yes"/&gt; &lt;xsl:strip-space elements="*"/&gt; &lt;xsl:key name="kContactByNameAddress" match="sms" use="concat(@contact_name,'+',@address)"/&gt; &lt;xsl:template match= "sms[generate-id() = generate-id(key('kContactByNameAddress', concat(@contact_name,'+',@address) ) [1] ) ] "&gt; &lt;sms contact_name="{@contact_name}"&gt; &lt;xsl:apply-templates mode="inGroup" select="key('kContactByNameAddress', concat(@contact_name,'+',@address) )"/&gt; &lt;/sms&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms" mode="inGroup"&gt; &lt;message type="{@type}"&gt; &lt;xsl:value-of select="@body"/&gt; &lt;/message&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms"/&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p><strong>When applied to the provided XML document</strong>:</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><strong>the wanted, correct result is produced</strong>:</p> <pre><code>&lt;sms contact_name="Person1"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person2"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person3"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; </code></pre> <p><strong>Update</strong>: The OP has edited his question and has posted new requirements that the <code>address</code> attribute may or maynot start with a country code. Two addresses, one with contry code and the other without country code are "the same" if the substring after the country code is equal to the other address. In this case the two elements should be grouped together.</p> <p><strong>Here is the solution</strong> (it would be trivial to write in XSLT 2.0, but in XSLT 1.0 to do so in a single pass is quite tricky. Amultipass solution is more easy, but it would generally require the <code>xxx:node-set()</code> extension function and would thus lose portability):</p> <pre><code>&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output omit-xml-declaration="yes" indent="yes"/&gt; &lt;xsl:strip-space elements="*"/&gt; &lt;xsl:key name="kContactByNameAddress" match="sms" use="concat(@contact_name,'+', concat(substring(@address, 4 div starts-with(@address,'+')), substring(@address, 1 div not(starts-with(@address,'+')) ) ) )"/&gt; &lt;xsl:template match= "sms[generate-id() = generate-id(key('kContactByNameAddress', concat(@contact_name,'+', concat(substring(@address, 4 div starts-with(@address,'+')), substring(@address, 1 div not(starts-with(@address,'+')) ) ) ) ) [1] ) ] "&gt; &lt;sms contact_name="{@contact_name}"&gt; &lt;xsl:apply-templates mode="inGroup" select="key('kContactByNameAddress', concat(@contact_name,'+', concat(substring(@address, 4 div starts-with(@address,'+')), substring(@address, 1 div not(starts-with(@address,'+')) ) ) ) ) "/&gt; &lt;/sms&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms" mode="inGroup"&gt; &lt;message type="{@type}"&gt; &lt;xsl:value-of select="@body"/&gt; &lt;/message&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms"/&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p><strong>When this transformation is applied on the following XML document</strong> (the previous one + added three <code>sms</code> elements with <code>contact_name="Jared"</code>, two of which have "identical" addresses, according to the newly posted rules):</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 contact_name="jared" address="12345" type="2" body="Some text"/&gt; &lt;sms contact_name="jared" address="56789" type="1" body="Some text"/&gt; &lt;sms contact_name="jared" address="+6412345" type="2" body="Some text"/&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><strong>the wanted, correct result is produced</strong>:</p> <pre><code>&lt;sms contact_name="Person1"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person2"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person3"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="jared"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="jared"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; </code></pre> <p><strong>Detailed explanation</strong>: </p> <p><strong>The main difficulty in this problem arises from the fact that there is no "if... then ... else" operator in XPath 1.0</strong>, however we must specify a single XPath expression in the <code>use</code> attribute of the <code>xsl:key</code> instruction, that either selects the <code>address</code> attribute (when it doesn't start with "+") or its substring after the country code (if its string value starts with "+").</p> <p><strong>Here I am using this poor man's implementation of</strong> </p> <pre><code>if($condition) then $string1 else $string2 </code></pre> <p><strong>The following XPath expression, when evaluated is equivalent to the above</strong>:</p> <pre><code>concat(substring($string1, 1 div $condition), substring($string2, 1 div not($condition)) ) </code></pre> <p>This equivalence follows from the fact that <code>1 div true()</code> is the same as <code>1 div 1</code> and this is <code>1</code>, while <code>1 div false()</code> is the same as <code>1 div 0</code> and that is the number (positive) <code>Infinity</code>.</p> <p>Also, for any string <code>$s</code>, the value of <code>substring($s, Infinity)</code> is just the empty string. And, of course, for any string <code>$s</code> the value of <code>substring($s, 1)</code> is just the string <code>$s</code> itself.</p> <p><strong>II. XSLT 2.0 solution</strong>:</p> <pre><code>&lt;xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:output omit-xml-declaration="yes" indent="yes"/&gt; &lt;xsl:strip-space elements="*"/&gt; &lt;xsl:template match="/*"&gt; &lt;xsl:for-each-group select="sms" group-by= "concat(@contact_name,'+', if(starts-with(@address,'+')) then substring(@address, 4) else @address )"&gt; &lt;sms contact_name="{@contact_name}"&gt; &lt;xsl:apply-templates select="current-group()"/&gt; &lt;/sms&gt; &lt;/xsl:for-each-group&gt; &lt;/xsl:template&gt; &lt;xsl:template match="sms"&gt; &lt;message type="{@type}"&gt; &lt;xsl:value-of select="@body"/&gt; &lt;/message&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p><strong>when this (much simpler!)XSLT 2.0 transformation is applied on the same XML document (above), the same correct output is produced</strong>:</p> <pre><code>&lt;sms contact_name="Person1"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person2"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="Person3"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="jared"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="jared"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="2"&gt;Some text&lt;/message&gt; &lt;/sms&gt; &lt;sms contact_name="(Unknown)"&gt; &lt;message type="1"&gt;Some text&lt;/message&gt; &lt;/sms&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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