Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This stylesheet will do as you ask. It uses a <code>key</code> together with <code>generate-id</code> to all the <code>ITEM010-</code> elements that are first for each <code>PackNumber</code>.</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt; &lt;xsl:strip-space elements="*"/&gt; &lt;xsl:output method="xml" indent="yes" encoding="UTF-8" omit-xml-declaration="yes"/&gt; &lt;xsl:key name="item-by-packnumber" match="ITEM010-" use="PACK050-/PackNumber"/&gt; &lt;xsl:template match="/"&gt; &lt;xsl:apply-templates select="Move-Afile/Afile"/&gt; &lt;/xsl:template&gt; &lt;xsl:template match="Afile"&gt; &lt;A&gt; &lt;target&gt; &lt;d1&gt;&lt;xsl:value-of select="HEAD010-/Receiver"/&gt;&lt;/d1&gt; &lt;d2&gt;81&lt;/d2&gt; &lt;d3&gt;&lt;xsl:value-of select="HEAD020-/DeliveryNoteNumber"/&gt;&lt;/d3&gt; &lt;d4&gt;&lt;xsl:value-of select="HEAD030-/BookingReference"/&gt;&lt;/d4&gt; &lt;d5&gt;&lt;xsl:value-of select="DELH040-/CustomerName"/&gt;&lt;/d5&gt; &lt;d6&gt;102&lt;/d6&gt; &lt;xsl:apply-templates select="ITEM010-[generate-id() = generate-id(key('item-by-packnumber', PACK050-/PackNumber)[1])]"/&gt; &lt;/target&gt; &lt;/A&gt; &lt;/xsl:template&gt; &lt;xsl:template match="ITEM010-"&gt; &lt;xsl:apply-templates mode="group" select="key('item-by-packnumber', PACK050-/PackNumber)"&gt; &lt;xsl:with-param name="sequence" select="2+count(preceding-sibling::ITEM010-[generate-id() = generate-id(key('item-by-packnumber', PACK050-/PackNumber)[1])])"/&gt; &lt;/xsl:apply-templates&gt; &lt;/xsl:template&gt; &lt;xsl:template match="ITEM010-" mode="group"&gt; &lt;xsl:param name="sequence"/&gt; &lt;CPS&gt; &lt;element1&gt;&lt;xsl:value-of select="$sequence"/&gt;&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;&lt;xsl:value-of select="position()"/&gt;&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;&lt;xsl:value-of select="ITEM030-/EANItemCode"/&gt;&lt;/element8&gt; &lt;xsl:copy-of select="PACK050-/PackNumber"/&gt; &lt;/LIN&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p><strong>output</strong></p> <pre><code>&lt;A&gt; &lt;target&gt; &lt;d1&gt;7612460142409&lt;/d1&gt; &lt;d2&gt;81&lt;/d2&gt; &lt;d3&gt;6125175001&lt;/d3&gt; &lt;d4&gt;F234&lt;/d4&gt; &lt;d5&gt;Office Customer&lt;/d5&gt; &lt;d6&gt;102&lt;/d6&gt; &lt;CPS&gt; &lt;element1&gt;2&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;1&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252380935&lt;/element8&gt; &lt;PackNumber&gt;PLT9180267&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;CPS&gt; &lt;element1&gt;2&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;2&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252888777&lt;/element8&gt; &lt;PackNumber&gt;PLT9180267&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;CPS&gt; &lt;element1&gt;3&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;1&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252312343&lt;/element8&gt; &lt;PackNumber&gt;PLT9763411&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;/target&gt; &lt;/A&gt; </code></pre> <hr> <p><strong>Update</strong></p> <p>This alternative works better, and produces the exact XML output that you require. However it is a little ungainly and uses the EXSLT <code>node-set</code> function which you may not have access to.</p> <p>It works by sorting the <code>ITEM010-</code> elements into variable <code>$items</code> and working from the data there using <code>exslt:node-set($items)</code> instead of directly from the source.</p> <p>Others may be able to suggest a better alternative.</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exsl="http://exslt.org/common" exclude-result-prefixes="exsl"&gt; &lt;xsl:strip-space elements="*"/&gt; &lt;xsl:output method="xml" indent="yes" encoding="UTF-8" omit-xml-declaration="yes"/&gt; &lt;xsl:key name="item-by-packnumber" match="ITEM010-" use="PACK050-/PackNumber"/&gt; &lt;xsl:template match="/"&gt; &lt;xsl:apply-templates select="Move-Afile/Afile"/&gt; &lt;/xsl:template&gt; &lt;xsl:template match="*" mode="copy"&gt; &lt;xsl:copy&gt; &lt;xsl:apply-templates mode="copy"/&gt; &lt;/xsl:copy&gt; &lt;/xsl:template&gt; &lt;xsl:template match="Afile"&gt; &lt;xsl:variable name="items"&gt; &lt;xsl:apply-templates select="ITEM010-" mode="copy"&gt; &lt;xsl:sort select="PACK050-/PackNumber"/&gt; &lt;/xsl:apply-templates&gt; &lt;/xsl:variable&gt; &lt;A&gt; &lt;target&gt; &lt;d1&gt;&lt;xsl:value-of select="HEAD010-/Receiver"/&gt;&lt;/d1&gt; &lt;d2&gt;81&lt;/d2&gt; &lt;d3&gt;&lt;xsl:value-of select="HEAD020-/DeliveryNoteNumber"/&gt;&lt;/d3&gt; &lt;d4&gt;&lt;xsl:value-of select="HEAD030-/BookingReference"/&gt;&lt;/d4&gt; &lt;d5&gt;&lt;xsl:value-of select="DELH040-/CustomerName"/&gt;&lt;/d5&gt; &lt;d6&gt;102&lt;/d6&gt; &lt;xsl:apply-templates select="exsl:node-set($items)/ITEM010-"/&gt; &lt;/target&gt; &lt;/A&gt; &lt;/xsl:template&gt; &lt;xsl:template match="ITEM010-"&gt; &lt;xsl:variable name="first-in-sequence" select="generate-id() = generate-id(key('item-by-packnumber', PACK050-/PackNumber)[1])"/&gt; &lt;xsl:variable name="group" select="2+count(preceding-sibling::ITEM010-[generate-id() = generate-id(key('item-by-packnumber', PACK050-/PackNumber)[1])])"/&gt; &lt;xsl:if test="$first-in-sequence"&gt; &lt;CPS&gt; &lt;element1&gt;&lt;xsl:value-of select="$group"/&gt;&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;/xsl:if&gt; &lt;LIN&gt; &lt;element6&gt;&lt;xsl:value-of select="position()"/&gt;&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;&lt;xsl:value-of select="ITEM030-/EANItemCode"/&gt;&lt;/element8&gt; &lt;xsl:copy-of select="PACK050-/PackNumber"/&gt; &lt;/LIN&gt; &lt;/xsl:template&gt; &lt;/xsl:stylesheet&gt; </code></pre> <p><strong>output</strong></p> <pre><code>&lt;A&gt; &lt;target&gt; &lt;d1&gt;7612460142409&lt;/d1&gt; &lt;d2&gt;81&lt;/d2&gt; &lt;d3&gt;6125175001&lt;/d3&gt; &lt;d4&gt;F234&lt;/d4&gt; &lt;d5&gt;Office Customer&lt;/d5&gt; &lt;d6&gt;102&lt;/d6&gt; &lt;CPS&gt; &lt;element1&gt;2&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;1&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252380935&lt;/element8&gt; &lt;PackNumber&gt;PLT9180267&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;LIN&gt; &lt;element6&gt;2&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252888777&lt;/element8&gt; &lt;PackNumber&gt;PLT9180267&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;CPS&gt; &lt;element1&gt;3&lt;/element1&gt; &lt;element2&gt;1&lt;/element2&gt; &lt;/CPS&gt; &lt;PAC&gt; &lt;element4&gt;111&lt;/element4&gt; &lt;element5&gt;012&lt;/element5&gt; &lt;/PAC&gt; &lt;LIN&gt; &lt;element6&gt;3&lt;/element6&gt; &lt;element7&gt;SV&lt;/element7&gt; &lt;element8&gt;5028252312343&lt;/element8&gt; &lt;PackNumber&gt;PLT9763411&lt;/PackNumber&gt; &lt;/LIN&gt; &lt;/target&gt; &lt;/A&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POXSLT 1.0 Logic grouping on nodes
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBorodin
    1. USBorodin
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COunder the <CPS> <element1>2</element1> <element2>1</element2> </CPS> my output contains two LIN elements because the PackNumber is same.Please check.
      singulars
      1. PO
      1. USsum
    2. COif PackNumber is equal we have to ignore the repeating of <CPS>,<PAC> elements.only one time it comes.
      singulars
      1. PO
      1. USsum
    3. CO@sum: Yes, I understand. And you want the output data sorted by `PackNumber`? The difficulty I have is producing the sequence number in `element6` according to the *output* order while grouping the elements at the same time. Do you have access to the EXSLT `node-set` function?
      singulars
      1. PO
      1. USBorodin
 

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