How to loop over in Xquery and compare the values within loops

RAMMII

I am new to Xquery , i have something tricky requirement.

Below is the sample xml for my requirement and tabled the values below. There are four(can be more ) titlcodes in each class type within network, from this i would like to form a key for each title code combination of  two networks and put them under titlecode range and max repeatable key will be at  Range at class. Provide input and expected xml also below.

Any pointers how to achieve that would be helpful, if not through x query is there any other approach to do this?

Classtype =23/ title codes	AA	AB	key
P1	100	100	100_100
P2	80	50	80_50
P3	80	80	80_80
P4	80	50	80_50

Input :

<root>

<ns1:Network>

    <ns1:description>AA</ns1:description>

    <ns1:classType>

        <ns1:class>23</ns1:class>

        <ns1:RangeAtClass>100</ns1:RangeAtClass>

        <ns1:title>                  

            <ns1:titleCode>P1</ns1:titleCode>                  

            <ns1:titleRange>100</ns1:titleRange>      

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P2</ns1:titleCode>

            <ns1:titleRange>80</ns1:titleRange>

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P3</ns1:titleCode>

            <ns1:titleRange>80</ns1:titleRange>

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P4</ns1:titleCode>

            <ns1:titleRange>80</ns1:titleRange>

        </ns1:title>      

    </ns1:classType>

</ns1:Network>

<ns1:Network>

    <ns1:description>AB</ns1:description>

    <ns1:classType>

        <ns1:class>23</ns1:class>

        <ns1:RangeAtClass>100%</ns1:RangeAtClass>

        <ns1:title>                  

            <ns1:titleCode>P1</ns1:titleCode>                  

            <ns1:titleRange>100</ns1:titleRange>      

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P2</ns1:titleCode>

            <ns1:titleRange>50</ns1:titleRange>

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P3</ns1:titleCode>

            <ns1:titleRange>80</ns1:titleRange>

        </ns1:title>

        <ns1:title>

            <ns1:titleCode>P4</ns1:titleCode>

            <ns1:titleRange>50</ns1:titleRange>

        </ns1:title>      

     </ns1:classType>  

</ns1:Network>

</root>

Expected OUTPUT:

<root>

<ns1:Network>

                <ns1:description>AA</ns1:description>

                <ns1:classType>

                                <ns1:class>23</ns1:class>

                                <ns1:RangeAtClass>80</ns1:RangeAtClass>   (first value of max occurrence key)

                                <ns1:title>                                                            

                                                <ns1:titleCode>P1</ns1:titleCode>                                                                      

                                                <ns1:titleRange>100_100</ns1:titleRange>                       

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P2</ns1:titleCode>

                                                <ns1:titleRange>80_50</ns1:titleRange>

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P3</ns1:titleCode>

                                                <ns1:titleRange>80_80</ns1:titleRange>

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P4</ns1:titleCode>

                                                <ns1:titleRange>80_50</ns1:titleRange>

                                </ns1:title>                      

                </ns1:classType>

</ns1:Network>

<ns1:Network>

                <ns1:description>AA</ns1:description>

                <ns1:classType>

                                <ns1:class>23</ns1:class>

                                <ns1:RangeAtClass>50</ns1:RangeAtClass> (second value of max occurrence key)

                                <ns1:title>                                                            

                                                <ns1:titleCode>P1</ns1:titleCode>                                                                      

                                                <ns1:titleRange>100_100</ns1:titleRange>                       

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P2</ns1:titleCode>

                                                <ns1:titleRange>80_50</ns1:titleRange>

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P3</ns1:titleCode>

                                                <ns1:titleRange>80_80</ns1:titleRange>

                                </ns1:title>

                                <ns1:title>

                                                <ns1:titleCode>P4</ns1:titleCode>

                                                <ns1:titleRange>80_50</ns1:titleRange>

                                </ns1:title>                      

                 </ns1:classType>          

</ns1:Network>

</root>

tsuji

Ok for the question. But you need to be more exact in the data so that people can get a clear picture of what you need,

.

[1] why the table shown (P3, AA)=80, but your xml suggests it being 50?

If your expected output shows 80_80 as ns1:titleRange for P3, the xml at (P3, AA) should be wrong.

[2] why the table shown (P4, AB)=50, but your xml suggests it being 80?

If your expected output shows 50 in ns1:RangeAtClass for AB, the xml at (P4, AB) should be wrong there.

And then if it is really 80 as shown, why then the expected output should show up 50 rather than 80 at ns1:RangeAtClass?

At least some consistency???

RAMMII

Thanks for response. sorry for bad data, i have updated the correct values.

Let me know the procedure for the output.

tsuji

The revised xml makes more sense and is then overall consistent in conception, as far as I can abstract out of the question.

Any pointers how to achieve that would be helpful, if not through x query is there any other approach to do this?

If you insist on using xquery, that is more clumsy as it would be more procedural oriented... I would suggest using XMLTransform to do it. Needless to say that XMLQuery and XMLTransform are of the same class of citizens as far as the querying oracle xmltype column or db, each has its range of ease in a query, In this case, I think, XMLTransform would be a more appropriate choice. The general idea would be that if you output practically the original xml except changes happened here and there at some focus points, I would tend to opt for XMLTransform, whereas if you output mostly those changes or original data at some focus points, not as wide-spread to the amplitude of practical the whole original, I would then tend to opt for XMLQuery instead.

Having said, even using XMLTransform here is not very straight-forward as xslt 1.0 is still the norm in that space and xslt 2.0+ is still something to desire. With xslt 2.0, it would look much easier to understand the underlying reasoning. The support of distinct-values(), string-join() and sequence make its coding much simpler than xslt 1.0 for this kind of functionality asked. I have no guarantee that you would understand it right-away.

Here is how it can be done in xslt 1.0. It is not the most efficient version which may use xsl:key construction. But for a reasonably shorter xmltype column or db, it is even preferrable due to its easier in reading directly from the script.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"    xmlns:ns="urn:unknown"><xsl:output method="xml" omit-xml-declaration="yes" encoding="utf-8" indent="yes" /><xsl:strip-space elements="*" /><xsl:template match="*|@*|text()"&gt;    <xsl:copy>        <xsl:apply-templates select="*|@*|text()" />    </xsl:copy></xsl:template><xsl:template match="ns:titleRange">    <xsl:variable name="klass" select="normalize-space(ancestor::ns:Network/ns:classType/ns:class)" />    <xsl:variable name="titleCode" select="normalize-space(preceding-sibling::ns:titleCode)" />    <xsl:variable name="sequence"         select="/root/ns:Network/ns:classType[normalize-space(ns:class)=$klass]/ns:title[normalize-space(ns:titleCode)=$titleCode]/ns:titleRange" />    <xsl:variable name="string-join">        <xsl:for-each select="$sequence">            <xsl:sort select="." data-type="number" order="descending" />            <xsl:choose>                <xsl:when test="position()=last()">                    <xsl:value-of select="normalize-space()" />                </xsl:when>                <xsl:otherwise>                    <xsl:value-of select="concat(normalize-space(),'_')" />                </xsl:otherwise>            </xsl:choose>        </xsl:for-each>    </xsl:variable>    <xsl:copy>        <xsl:apply-templates select="@*" />        <xsl:value-of select="$string-join" />    </xsl:copy></xsl:template><xsl:template match="ns:RangeAtClass">    <xsl:variable name="all-sequence"        select="ancestor::ns:Network/ns:classType/ns:title/ns:titleRange[number()!=number(current()/ancestor::ns:Network/following::ns:titleRange)]" />    <xsl:variable name="max">        <xsl:for-each select="$all-sequence">            <xsl:sort select="count(current()/ancestor::ns:Network/ns:classType/ns:title/ns:titleRange[.=current()])" order="descending" />            <xsl:choose>                <xsl:when test="position()=1">                    <xsl:value-of select="." />                </xsl:when>                <xsl:otherwise />            </xsl:choose>        </xsl:for-each>    </xsl:variable>    <xsl:copy>        <xsl:apply-templates select="@*" />        <xsl:value-of select="$max" />    </xsl:copy></xsl:template></xsl:stylesheet>

Here I suppose the namespace of ns1 in the original xml document be urn:unknown. Replace it with your proper namespace uri.

With it as an external file, you load it to perform the query... like this.

create or replace directory xdir as 'c:\yourDirectoryToXsl';-- suppose your db be xmltype db or a db with column testdoc being of xmltype storing the xml input doc, -- with another column id being 1 to query upon...-- it would look something like this...SELECT x.testdoc.transform(XMLType(bfilename('XDIR', 'theXsltFile.xsl'), nls_charset_id('AL32UTF8'))).getStringVal() as outputFROM yourDB x where id='1';

That's about it.

odie_63

You're not saying whether you expect a solution compatible with Oracle Database XQuery implementation.

Anyhow...

Using XQuery only, it's relatively easy with XQuery Update features :

declare namespace ns1 = "dummy";declare function local:getMaxOccurring($items as element()*) as xs:string{  ( for $tr in fn:distinct-values($items)     order by fn:count($items[.=$tr]) descending    return $tr )[1]};copy $d := $inputDocmodify (  for $i in $d/root/ns1:Network[1]/ns1:classType/ns1:title    , $j in $d/root/ns1:Network[2]/ns1:classType/ns1:title  let $key := fn:concat($i/ns1:titleRange, "_", $j/ns1:titleRange)  where $i/ns1:titleCode = $j/ns1:titleCode             return (     replace value of node $i/ns1:titleRange with $key  , replace value of node $j/ns1:titleRange with $key  ), for $i in $d/root/ns1:Network/ns1:classType  return replace value of node $i/ns1:RangeAtClass             with local:getMaxOccurring($i/ns1:title/ns1:titleRange))return $d

I find the solution much more intuitive and less convoluted than an equivalent XSLT 1.0 approach.

Using the above from within Oracle would look like this :

SQL> with tmp as (  2    select xmltype('<root xmlns:ns1="dummy">  3  <ns1:Network>  4      <ns1:description>AA</ns1:description>  5      <ns1:classType>  6          <ns1:class>23</ns1:class>  7          <ns1:RangeAtClass>100</ns1:RangeAtClass>  8          <ns1:title>  9              <ns1:titleCode>P1</ns1:titleCode> 10              <ns1:titleRange>100</ns1:titleRange> 11          </ns1:title> 12          <ns1:title> 13              <ns1:titleCode>P2</ns1:titleCode> 14              <ns1:titleRange>80</ns1:titleRange> 15          </ns1:title> 16          <ns1:title> 17              <ns1:titleCode>P3</ns1:titleCode> 18              <ns1:titleRange>80</ns1:titleRange> 19          </ns1:title> 20          <ns1:title> 21              <ns1:titleCode>P4</ns1:titleCode> 22              <ns1:titleRange>80</ns1:titleRange> 23          </ns1:title> 24      </ns1:classType> 25  </ns1:Network> 26  <ns1:Network> 27      <ns1:description>AB</ns1:description> 28      <ns1:classType> 29          <ns1:class>23</ns1:class> 30          <ns1:RangeAtClass>100%</ns1:RangeAtClass> 31          <ns1:title> 32              <ns1:titleCode>P1</ns1:titleCode> 33              <ns1:titleRange>100</ns1:titleRange> 34          </ns1:title> 35          <ns1:title> 36              <ns1:titleCode>P2</ns1:titleCode> 37              <ns1:titleRange>50</ns1:titleRange> 38          </ns1:title> 39          <ns1:title> 40              <ns1:titleCode>P3</ns1:titleCode> 41              <ns1:titleRange>80</ns1:titleRange> 42          </ns1:title> 43          <ns1:title> 44              <ns1:titleCode>P4</ns1:titleCode> 45              <ns1:titleRange>50</ns1:titleRange> 46          </ns1:title> 47       </ns1:classType> 48  </ns1:Network> 49  </root>') doc 50    from dual 51  ) 52  select xmlserialize(document 53           xmlquery( 54             'declare namespace ns1 = "dummy"; (::) 55              declare function local:getMaxOccurring($items as element()*) as xs:string 56              { 57                ( for $tr in fn:distinct-values($items) 58                  order by fn:count($items[.=$tr]) descending 59                  return $tr )[1] 60              }; (::) 61              copy $d := . 62              modify ( 63                for $i in $d/root/ns1:Network[1]/ns1:classType/ns1:title 64                  , $j in $d/root/ns1:Network[2]/ns1:classType/ns1:title 65                let $key := fn:concat($i/ns1:titleRange,"_",$j/ns1:titleRange) 66                where $i/ns1:titleCode = $j/ns1:titleCode 67                return ( 68                  replace value of node $i/ns1:titleRange with $key 69                , replace value of node $j/ns1:titleRange with $key 70                ) 71              , for $i in $d/root/ns1:Network/ns1:classType 72                return replace value of node $i/ns1:RangeAtClass 73                          with local:getMaxOccurring($i/ns1:title/ns1:titleRange) 74              ) 75              return $d' 76             passing doc 77             returning content 78           ) 79           indent 80         ) 81  from tmp;XMLSERIALIZE(DOCUMENTXMLQUERY(--------------------------------------------------------------------------------<root xmlns:ns1="dummy">  <ns1:Network>    <ns1:description>AA</ns1:description>    <ns1:classType>      <ns1:class>23</ns1:class>      <ns1:RangeAtClass>80</ns1:RangeAtClass>      <ns1:title>        <ns1:titleCode>P1</ns1:titleCode>        <ns1:titleRange>100_100</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P2</ns1:titleCode>        <ns1:titleRange>80_50</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P3</ns1:titleCode>        <ns1:titleRange>80_80</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P4</ns1:titleCode>        <ns1:titleRange>80_50</ns1:titleRange>      </ns1:title>    </ns1:classType>  </ns1:Network>  <ns1:Network>    <ns1:description>AB</ns1:description>    <ns1:classType>      <ns1:class>23</ns1:class>      <ns1:RangeAtClass>50</ns1:RangeAtClass>      <ns1:title>        <ns1:titleCode>P1</ns1:titleCode>        <ns1:titleRange>100_100</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P2</ns1:titleCode>        <ns1:titleRange>80_50</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P3</ns1:titleCode>        <ns1:titleRange>80_80</ns1:titleRange>      </ns1:title>      <ns1:title>        <ns1:titleCode>P4</ns1:titleCode>        <ns1:titleRange>80_50</ns1:titleRange>      </ns1:title>    </ns1:classType>  </ns1:Network></root>

tsuji

It is indeed a good and needy, the update functionality, xquery technology ought to have and since then is supported. There is no question about it.

Otherwise, I want to make a little note, just to make things value to what they are worth, is that the xslt approach for this (specific - to be more focus and not diverting attention) problem is that the cardinality of ns:Network is in no way restricted to 2. It can take on any number, in the language of w3c schema being @maxOccurs="unbounded" as will. Furthermore, in the xslt2, the xslt script would be substantially more concise, However, xslt1 version is, to me the least, always of interest in any circumstances as we encounter that many opinions out there and that it would be a big misunderstanding to put up an impression that xslt1 is somehow unable to handle many situations that it actually can.,,