Xquery help sorted parent child relationship_问答_开发者

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>

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>

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>
}