Xquery help sorted parent child relationship

2023-03-06 08:05 问答作者：

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

继续阅读：xquery

Xquery help sorted parent child relationship

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？