Xquery Help Sorts Parent Relationships

Question

I have the following xml (simplified and anonymous from real input), it basically contains a list of policies that have a policy start date, a policy link and a parent policy link (indicating 0 without a parent)

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

• Oldest policy on top (oldest start date)
  • If he has children, his children should follow (also ordered by oldest start date)
• Following the next oldest non-affiliated policy
  • If he has children, his children should follow (also ordered by oldest start date)
• And repeat

In fact, I’m all in a dead end, I tried different things, but here is my last 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> } 

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> 

Output required

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

Answer 1

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

creates the desired 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> 

Answer 2

Believe me, I finally answered.

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