How to match and merge XML in database?

user_16883181 0
Viewed 356

I have below mixed documents in one collection.

B3:
    <creditRisk>
      <characteristic>
        <score>
            <LID>C230</LID>
            <SPID>129587</SPID>
            <Sector>Finance and Insurance</Sector>
        </score>
        <score>
            <LID>C177</LID>
            <SPID>360720</SPID>
            <Sector>Mining and Oil and Gas Extraction</Sector>
        </score>
      </characteristic>
    </creditRisk>
 
B4:
    <creditRisk>
      <pit>
        <score>
            <LID>C230</LID>           
            <SPID>129587</SPID>
            <LTV>1.4689503</LTV>
            <LGD>0.5995806998</LGD>
            <Logarithm>-0.915243031</Logarithm>
        </score>
        <score>
            <LID>C177</LID>            
            <SPID>360720</SPID>
            <LTV>1.524224737</LTV>
            <LGD>0.8989534312</LGD>
            <Logarithm>-2.292173791</Logarithm>
        </score>
      </pit>
</creditRisk>

At the moment to simplify the problem, I need to merge pit/score@B4 when its SPID equals to characteristic/score/SPID@B3 inside MarkLogic. 

Expected Output:

<characteristic>
   <score>
      <default>
         <LID>C230</LID>
         <SPID>129587</SPID>
         <LTV>1.4689503</LTV>
         <LGD>0.5995806998</LGD>
         <Logarithm>-0.915243031</Logarithm>
      </default>
      <LID>C230</LID>
      <SPID>129587</SPID>
      <Sector>Finance and Insurance</Sector>
   </score>
   <score>
      <default>
         <LID>C177</LID>
         <SPID>360720</SPID>
         <LTV>1.524224737</LTV>
         <LGD>0.8989534312</LGD>
         <Logarithm>-2.292173791</Logarithm>
      </default>
      <LID>C177</LID>
      <SPID>360720</SPID>
      <Sector>Mining and Oil and Gas Extraction</Sector>
   </score>
</characteristic>

We are facing issue. My xsl comes out all blank results.

    <xsl:template match="characteristic">   
        <characteristic>
        <xsl:call-template name="scoreSPID">
          <xsl:with-param name="characterScore" select="score"/>
        </xsl:call-template>
        </characteristic>
    </xsl:template>
 
    <xsl:template name="scoreSPID">
        <xsl:param name="characterScore"/>
        <xsl:for-each select="$characterScore">
        <xsl:variable name="spid" select="SPID"/>
            <score>
              <xsl:for-each select="/creditRisk/pit/score[SPID eq $spid]">
              <default>
                <xsl:copy-of select="./node()"/>
              </default>
              <xsl:copy-of select="node()"/>
              </xsl:for-each>
            </score>     
        </xsl:for-each>
    </xsl:template>  
   
    <xsl:template match="node()">
      <xsl:apply-templates/>
</xsl:template>

How can I get the match/merge work in my xsl? Do note B3 and B4 are different dokuments in the same database.

marklogic nosql xslt
2 Answers
  <xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:fn="http://www.w3.org/2005/xpath-functions"
    xmlns:cts="http://marklogic.com/cts" xmlns:fff="schema://fc.fasset/functions"
    exclude-result-prefixes="#all" version="2.0">
    
    <xsl:function name="fff:mergeModelI">
        <xsl:param name="characterScore"/>
        <xsl:for-each select="$characterScore">  
            <xsl:variable name="spid" select="SPID"/>
            <xsl:variable name="query"
                select="cts:path-range-query('/creditRisk/pit/score/SPID', '=', $spid)"/>
            <xsl:variable name="model" 
                select="cts:search(fn:collection('collection-name')/creditRisk/pit/score, $query)"/>
            <xsl:choose>
                <xsl:when test="exists($model)">
                    <score>
                        <matched>merged</matched>
                        <default>
                            <xsl:copy-of select="$model/node()"/>
                        </default>
                        <xsl:copy-of select="node()"/>
                    </score>
                </xsl:when>
                <xsl:otherwise/>
            </xsl:choose>          
        </xsl:for-each>    
    </xsl:function>
    
    <xsl:template match="characteristic">    
        <characteristic> 
            <xsl:sequence select="fff:mergeModelI(score)"/> 
        </characteristic>
    </xsl:template> 
    
    <xsl:template match="node()">
        <xsl:apply-templates/>
    </xsl:template>   
    
  </xsl:transform>

If you are looking at continuous integration, the matched/merged document should be ingested in another database with distinct collection(s).

The aforesaid and data governance/auditing necessitate the match/merge operation tracking. Here I tag <matched>merged</matched> as a recourse. The matched/merged document can be populated in another database based on the tag. You can design more comprehensive canonicalization to suit your needs.

user_123095190

In the predicate filter for the for-each:

<xsl:for-each select="/creditRisk/pit/score[SPID eq spid]">

you want to filter where the SPID is equal to the variable $spid. Without the $ it is looking for a sibling element spid (which doesn't exist).

It should be:

<xsl:for-each select="/creditRisk/pit/score[SPID eq $spid]">
Mads_Hansen_144190
Related