Xquery help sorted parent child relationship

Question

I have the below xml (simplified and anonymized from real input) it basically contains a list of policies which have a policy start date, policy reference, and a parent policy reference (with 0 indicating no parent)

What I am trying to achieve is the output of the form.

The oldest policy on top (oldest start date)
- If it has children it’s children must follow (also ordered by oldest start date)
Followed by the next oldest non-child policy
- If it has children it’s children must follow (also ordered by oldest start date)
And repeat

It actually has me stumped, I've tried various things, but here is my latest attempt.

{
let $rows:= for $x in SOAP-ENV:Envelope/SOAP-ENV:Body/cus:GetCustPolicyResponse/cus:Policy/cus:PolicyRow
order by $x/cus:DateStart
return $x
for$policy in distinct-values($rows/cus:PolRef)
for $parentPolicy in distinct-values($rows/cus:parentPolRef)
        for $row in $rows
        where $row/cus:parentPolRef =$parentPolicy  and $row/cus:PolRef =$policy
        return <tr>
                <td>{$row/cus:PolRef/text()}</td>
                <td>{$row/cus:parentPolRef/text()}</td>
                <td>{$row/cus:DateStart/text()}</td>
                </tr>
}

The XML

<SOAP-ENV:Envelope xmlns:SOAP-ENV="hp://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="hp://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="hp://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <GetCustPolicyResponse xmlns="hp://www.client.com/services/customer">
         <Policy>
            <PolicyRow>
               <PolRef>1</PolRef>
               <DateStart>2011-04-01</DateStart>
               <parentPolRef>0</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>2</PolRef>
               <DateStart>2011-04-01</DateStart>
               <parentPolRef>0</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>3</PolRef>
               <DateStart>2011-04-20</DateStart>
               <parentPolRef>0</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>20</PolRef>
               <DateStart>2011-04-02</DateStart>
               <parentPolRef>1</parentPolRef>
            </PolicyRow>
             <PolicyRow>
               <PolRef>21</PolRef>
               <DateStart>2011-04-01</DateStart>
               <parentPolRef>1</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>26</PolRef>
               <DateStart>2011-04-22</DateStart>
               <parentPolRef>3</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>4</PolRef>
               <DateStart>2011-04-03</DateStart>
               <parentPolRef>0</parentPolRef>
            </PolicyRow>
        <PolicyRow>
               <PolRef>25</PolRef>
               <DateStart>2011-04-21</DateStart>
               <parentPolRef>3</parentPolRef>
            </PolicyRow>
            <PolicyRow>
               <PolRef>24</PolRef>
               <DateStart>2011-04-16</DateStart>
               <parentPolRef>2</parentPolRef>
            </PolicyRow>
             <PolicyRow>
               <PolRef>23</PolRef>
               <DateStart>2011-04-17</DateStart>
               <parentPolRef>2</parentPolRef>
            </PolicyRow>
         </Policy>
      </GetCustPolicyResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Wanted Output

<table>
<tr>
    <td>Policy Reference</td>
    <td>Policy start date</td>
</tr>
<tr>
    <td>1</td>
    <td>2011-04-01</td>
</tr>
<tr>
    <td>21</td>
    <td>2011-04-21</td>
</tr>
<tr>
    <td>20</td>
    <td>2011-04-02</td>
</tr>
<tr>
    <td>2</td>
    <td>2011-04-01</td>
</tr>
<tr>
    <td>24</td>
    <td>2011-04-16</td>
</tr>
<tr>
    <td>23</td>
    <td>2011-04-17</td>
</tr>
<tr>
    <td>4</td>
    <td>2011-04-03</td>
</tr>
<tr>
    <td>3</td>
    <td>2011-04-20</td>
</tr>
<tr>
    <td>25/td>
    <td>2011-04-21</td>
</tr>
<tr>
    <td>26</td>
    <td>2011-04-22</td>
</tr>

Please, provide the exact output you want to be produced. Also, such kind of tasks are usually much easier to solve with XSLT. Do you want to look into an XSLT solution of this problem? — Dimitre Novatchev, May 14 '11 at 14:44
I'll edit the question with the exact output. Sadly XSLT is not an option for technical reasons (restrictions of a third party technology stack) — Kevin D, May 14 '11 at 15:56
@Kevin D: This output contradicts the requirement that the newest policy must be at the top -- I was expectin policy no. 3 to be at the top. ??? — Dimitre Novatchev, May 14 '11 at 18:35
Good question, +1. I first came up with easy and short XSLT solution, then translated it into XQuery, which is still not bad -- please, have a look at my answer. — Dimitre Novatchev, May 15 '11 at 02:10
Thanks very much @Dimitre, you are correct I've made a mistake in my question, unfortunately the mistake is in the requirements, It should be oldest first. Looking at your answer, it should just mean switching descending for ascending, yes? — Kevin D, May 15 '11 at 09:39

Dimitre Novatchev · Accepted Answer · 2011-05-15T04:45:58.253

I. This XQuery code:

declare namespace  x = "hp://www.client.com/services/customer";  
declare function x:PolicyByParentRef($pNodes as element()*, 
                                     $pRef as xs:string) as element()*
{
  $pNodes[x:parentPolRef eq $pRef]
};

declare function x:ProcessPolicy($pNodes as element()*, 
                                 $pPol as element()) as element()*
{
 if(not(empty($pPol)))
   then  
    (<tr>
        <td>{$pPol/x:PolRef/text()}</td>,
        <td>{$pPol/x:DateStart/text()}</td>
      </tr>,
     for $child-policy in x:PolicyByParentRef($pNodes, $pPol/x:PolRef)
       order by $child-policy/x:DateStart descending
       return
            x:ProcessPolicy($pNodes, $child-policy)
    )
  else ()
};
<table>
{for $topPolicy in  x:PolicyByParentRef(/*/*/*/*/x:PolicyRow,  '0')
    order by $topPolicy/x:DateStart  descending
   return
       x:ProcessPolicy(/*/*/*/*/x:PolicyRow, $topPolicy)
 }
</table>

when applied on the provided XML document:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="hp://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="hp://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="hp://www.w3.org/2001/XMLSchema">
  <SOAP-ENV:Body>
    <GetCustPolicyResponse xmlns="hp://www.client.com/services/customer">
      <Policy>
        <PolicyRow>
          <PolRef>1</PolRef>
          <DateStart>2011-04-01</DateStart>
          <parentPolRef>0</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>2</PolRef>
          <DateStart>2011-04-01</DateStart>
          <parentPolRef>0</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>3</PolRef>
          <DateStart>2011-04-20</DateStart>
          <parentPolRef>0</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>20</PolRef>
          <DateStart>2011-04-02</DateStart>
          <parentPolRef>1</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>21</PolRef>
          <DateStart>2011-04-01</DateStart>
          <parentPolRef>1</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>26</PolRef>
          <DateStart>2011-04-22</DateStart>
          <parentPolRef>3</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>4</PolRef>
          <DateStart>2011-04-03</DateStart>
          <parentPolRef>0</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>25</PolRef>
          <DateStart>2011-04-21</DateStart>
          <parentPolRef>3</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>24</PolRef>
          <DateStart>2011-04-16</DateStart>
          <parentPolRef>2</parentPolRef>
        </PolicyRow>
        <PolicyRow>
          <PolRef>23</PolRef>
          <DateStart>2011-04-17</DateStart>
          <parentPolRef>2</parentPolRef>
        </PolicyRow>
      </Policy>
    </GetCustPolicyResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

produces the wanted result:

<?xml version="1.0" encoding="UTF-8"?>
<table>
   <tr>
      <td>3</td>,
        <td>2011-04-20</td>
   </tr>
   <tr>
      <td>26</td>,
        <td>2011-04-22</td>
   </tr>
   <tr>
      <td>25</td>,
        <td>2011-04-21</td>
   </tr>
   <tr>
      <td>4</td>,
        <td>2011-04-03</td>
   </tr>
   <tr>
      <td>1</td>,
        <td>2011-04-01</td>
   </tr>
   <tr>
      <td>20</td>,
        <td>2011-04-02</td>
   </tr>
   <tr>
      <td>21</td>,
        <td>2011-04-01</td>
   </tr>
   <tr>
      <td>2</td>,
        <td>2011-04-01</td>
   </tr>
   <tr>
      <td>23</td>,
        <td>2011-04-17</td>
   </tr>
   <tr>
      <td>24</td>,
        <td>2011-04-16</td>
   </tr>
</table>

II. Just for comparison - the XSLT solution:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:x="hp://www.client.com/services/customer"
 exclude-result-prefixes="x">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/>

 <xsl:key name="kPolicyByRef" match="x:PolicyRow"
  use="x:parentPolRef"/>

 <xsl:template match="/">
  <table>
   <xsl:apply-templates select=
    "key('kPolicyByRef', '0')">
     <xsl:sort select="x:DateStart" order="descending"/>
   </xsl:apply-templates>
  </table>
 </xsl:template>

 <xsl:template match="x:PolicyRow">
  <tr>
    <xsl:apply-templates/>
  </tr>

  <xsl:apply-templates select=
  "key('kPolicyByRef', x:PolRef)">
   <xsl:sort select="x:DateStart" order="descending"/>
  </xsl:apply-templates>
 </xsl:template>

 <xsl:template match="x:PolicyRow/*">
  <td><xsl:value-of select="."/></td>
 </xsl:template>

 <xsl:template match="x:parentPolRef" priority="2"/>
</xsl:stylesheet>

@Kevin D: You are welcome. I wonder if just sorting all nodes only once is sufficient -- this would be the most efficient solution. — Dimitre Novatchev, May 15 '11 at 13:45

score 0 · Answer 2 · answered May 14 '11 at 17:23

Believe I have the answer finally.

{
let $rows:= for $x in SOAP-ENV:Envelope/SOAP-ENV:Body/cus:GetCustPolicyResponse/cus:Policy/cus:PolicyRow
order by $x/cus:DateStart
return $x

let $parentRows := for $x in SOAP-ENV:Envelope/SOAP-ENV:Body/cus:GetCustPolicyResponse/cus:Policy/cus:PolicyRow
where  $x/cus:parentPolRef = 0
order by $x/cus:DateStart
return $x

for $parentPolicy in $parentRows
    let $children := 
        for $row in $rows
        where$parentPolicy/cus:PolRef = $row/cus:parentPolRef 
        return
        <tr>
        <td style="left-padding 20px;">child {$row/cus:PolRef}</td>
        <td> {$row/cus:DateStart}</td>
        </tr>
return
    <tr>
     <td>parent {$parentPolicy/cus:PolRef }</td>
     <td>{$parentPolicy/cus:DateStart }</td>
    {$children}
    </tr>
}

Xquery help sorted parent child relationship

2 Answers2

Linked