Forum Stats

  • 3,828,064 Users
  • 2,260,856 Discussions
  • 7,897,450 Comments

Discussions

Extract XML Values - order by element

3174034
3174034 Member Posts: 3
edited Apr 21, 2016 9:09AM in SQL & PL/SQL

Hi All,

I want to extract the XML values in the order, how the XML elements are presented. See the sample XML below. the number of elements <GroupOperator> and <properties> are unknown and dynamically generated. I am generating WHERE clause Query using this tags.

I am able to extract values using XMLTABLE() for both, but the order is the problem here. Expected output is like below.Many Thanks.

Note: <GroupOperator> and <properties> elements are come in any order, i.e <properties> element may come after <GroupOperator> and vice versa.


WHERE (country = Austria AND cn_id=000821)  -- <Group operator> values

     AND generic_name = ABT-510         -- <properties> Values

    AND  generic_name1= ABT-5101


              <SearchCriteria>

                                <sortOrder></sortOrder>

                                <LogicalOperator>AND</LogicalOperator>

                                <recordLimit>1000</recordLimit>

                                <GroupOperator>

                                    <GroupOperatorValue>AND</GroupOperatorValue>

                                     <properties>

                                        <propertySourceName>country</propertySourceName>

                                        <Operator>=</Operator>

                                        <value>Austria</value>

                                        </properties>

                                     <properties>

                                       <propertySourceName>cn_id</propertySourceName>

                                       <Operator>=</Operator>

                                       <value>000821</value>

                                     </properties> 

                                 </GroupOperator>

                                <properties>

                                    <propertySourceName>generic_name</propertySourceName>

                                    <Operator>=</Operator>

                                    <value>ABT-510</value>

                                </properties> 

                                 <properties>

                                     <propertySourceName>generic_name1</propertySourceName>

                                     <Operator>=</Operator>

                                     <value>ABT-5101</value>

                                </properties>

         </SearchCriteria>'

padderschris227Paulzip

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 21, 2016 3:41AM

    Interesting.

    Are nested <GroupOperator> allowed?

    For example :

    (country = 'Austria' AND ( cn_id = '000821' OR x = '1') )


    If so, what would the corresponding XML fragment look like?

    It's important since it'll decide whether we have to use a recursive approach or not.

  • 3174034
    3174034 Member Posts: 3
    edited Apr 21, 2016 3:57AM

    Hi,

    As of now, we dont use nested <GroupOperator>.  Many Thanks.

  • Paulzip
    Paulzip Member Posts: 8,695 Blue Diamond
    edited Apr 21, 2016 4:13AM
    3174034 wrote:
    
    As of now, we dont use nested <GroupOperator>. 
    

    Famous last words.  Solutions should always be designed with extensibility in mind.

    I've seen this type of format before, well similar-ish and it was definitely recursive, so you can have groups within groups.

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 21, 2016 4:19AM
    As of now, we dont use nested <GroupOperator>.
    

    OK.

    This will handle it in case you use it in the future :

    SQL> set define off
    SQL>
    SQL>
    SQL> with sample_data (xmlcontent) as (
      2    select xmlparse(content
      3  '<SearchCriteria>
      4    <sortOrder></sortOrder>
      5    <LogicalOperator>AND</LogicalOperator>
      6    <recordLimit>1000</recordLimit>
      7    <GroupOperator>
      8      <GroupOperatorValue>AND</GroupOperatorValue>
      9       <properties>
     10          <propertySourceName>country</propertySourceName>
     11          <Operator>=</Operator>
     12          <value>Austria</value>
     13          </properties>
     14       <properties>
     15         <propertySourceName>cn_id</propertySourceName>
     16         <Operator>=</Operator>
     17         <value>000821</value>
     18       </properties>
     19     </GroupOperator>
     20    <properties>
     21        <propertySourceName>generic_name</propertySourceName>
     22        <Operator>=</Operator>
     23        <value>ABT-510</value>
     24    </properties>
     25     <properties>
     26         <propertySourceName>generic_name1</propertySourceName>
     27         <Operator>=</Operator>
     28         <value>ABT-5101</value>
     29    </properties>
     30  </SearchCriteria>')
     31    from dual
     32  )
     33  select x.*
     34  from sample_data t
     35     , xmltable(
     36         'declare function local:processProp ($p as element(properties)) as xs:string {
     37            concat($p/propertySourceName, $p/Operator, "&apos;", $p/value, "&apos;")
     38          }; (::)
     39          declare function local:processGroup ($g as element(GroupOperator)) as xs:string {
     40            concat( "("
     41                  , local:dispatch($g/(properties|GroupOperator), $g/GroupOperatorValue)
     42                  , ")" )
     43          }; (::)
     44          declare function local:dispatch ($items as element()*, $op as xs:string) as xs:string {
     45            string-join(
     46              for $i in $items
     47              return typeswitch ($i)
     48                       case element(properties) return local:processProp($i)
     49                       case element(GroupOperator) return local:processGroup($i)
     50                       default return ""
     51            , concat(" ", $op, " ")
     52            )
     53          }; (::)
     54          local:dispatch( /SearchCriteria/(properties|GroupOperator)
     55                        , /SearchCriteria/LogicalOperator )'
     56         passing t.xmlcontent
     57         columns output varchar2(4000) path '.'
     58       ) x
     59  ;
    
    OUTPUT
    ------------------------------------------------------------------------------------------------------
    (country='Austria' AND cn_id='000821') AND generic_name='ABT-510' AND generic_name1='ABT-5101'
    
    
    
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 21, 2016 4:28AM

    Due to the recursive nature of the language, that's also a perfect job for XSLT.

    Will post an example later if I have time.

  • padders
    padders Member Posts: 1,076 Silver Trophy
    edited Apr 21, 2016 5:09AM

    No overloading support in xquery? Shame.

    chris227
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 21, 2016 5:47AM
    padders wrote:
    
    No overloading support in xquery? Shame.
    
    

    Well, you can have two functions with the same name and different number of arguments but that's all about it.

    But your comment makes me realize that my example is overdetailed.

    We can actually use a single recursive function :

    declare function local:dispatch ($items as element()*, $op as xs:string) as xs:string {
              string-join(
                for $i in $items
                return typeswitch ($i)
                        case element(properties) return concat($i/propertySourceName, $i/Operator, "&apos;", $i/value, "&apos;")
                        case element(GroupOperator) return concat( "("
                                                                  , local:dispatch($i/(properties|GroupOperator), $i/GroupOperatorValue)
                                                                  , ")" )
                        default return ""
              , concat(" ", $op, " ")
              )
            };
    

    Here, the typeswitch directive kind of simulates the dynamic binding we can achieve with true overloaded functions.

    Putting it all together (with an additional nested group) :

    SQL> with sample_data (xmlcontent) as (
      2    select xmlparse(content
      3  '<SearchCriteria>
      4    <sortOrder></sortOrder>
      5    <LogicalOperator>AND</LogicalOperator>
      6    <recordLimit>1000</recordLimit>
      7    <GroupOperator>
      8        <GroupOperatorValue>AND</GroupOperatorValue>
      9         <properties>
     10            <propertySourceName>country</propertySourceName>
     11            <Operator>=</Operator>
     12            <value>Austria</value>
     13            </properties>
     14         <properties>
     15           <propertySourceName>cn_id</propertySourceName>
     16           <Operator>=</Operator>
     17           <value>000821</value>
     18         </properties>
     19        <GroupOperator>
     20          <GroupOperatorValue>OR</GroupOperatorValue>
     21           <properties>
     22              <propertySourceName>X</propertySourceName>
     23              <Operator>=</Operator>
     24              <value>1</value>
     25              </properties>
     26           <properties>
     27             <propertySourceName>Y</propertySourceName>
     28             <Operator>=</Operator>
     29             <value>2</value>
     30           </properties>
     31       </GroupOperator>
     32     </GroupOperator>
     33    <properties>
     34        <propertySourceName>generic_name</propertySourceName>
     35        <Operator>=</Operator>
     36        <value>ABT-510</value>
     37    </properties>
     38     <properties>
     39         <propertySourceName>generic_name1</propertySourceName>
     40         <Operator>=</Operator>
     41         <value>ABT-5101</value>
     42    </properties>
     43  </SearchCriteria>')
     44    from dual
     45  )
     46  select x.*
     47  from sample_data t
     48     , xmltable(
     49         'declare function local:dispatch ($items as element()*, $op as xs:string) as xs:string {
     50            string-join(
     51              for $i in $items
     52              return typeswitch ($i)
     53                       case element(properties) return concat($i/propertySourceName, $i/Operator, "&apos;", $i/value, "&apos;")
     54                       case element(GroupOperator) return concat( "("
     55                                                                , local:dispatch($i/(properties|GroupOperator), $i/GroupOperatorValue)
     56                                                                , ")" )
     57                       default return ""
     58            , concat(" ", $op, " ")
     59            )
     60          }; (::)
     61          local:dispatch( /SearchCriteria/(properties|GroupOperator)
     62                        , /SearchCriteria/LogicalOperator )'
     63         passing t.xmlcontent
     64         columns output varchar2(4000) path '.'
     65       ) x
     66  ;
    
    OUTPUT
    -----------------------------------------------------------------------------------------------------------------------
    (country='Austria' AND cn_id='000821' AND (X='1' OR Y='2')) AND generic_name='ABT-510' AND generic_name1='ABT-5101'
    
    
    padderschris227Paulzip
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 21, 2016 9:09AM

    And here's the XSLT way :

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:variable name="apos">&apos;</xsl:variable>
      <xsl:template match="/SearchCriteria">
        <xsl:apply-templates select="properties|GroupOperator">
          <xsl:with-param name="op" select="LogicalOperator"/>
        </xsl:apply-templates>
      </xsl:template>
      <xsl:template match="properties|GroupOperator">
        <xsl:param name="op"/>
        <xsl:if test="position()>1"><xsl:value-of select="concat(' ',$op,' ')"/></xsl:if>
        <xsl:apply-templates select="." mode="normal"/>
      </xsl:template>
      <xsl:template match="GroupOperator" mode="normal">
        <xsl:text>(</xsl:text>
          <xsl:apply-templates select="properties|GroupOperator">
            <xsl:with-param name="op" select="GroupOperatorValue"/>
          </xsl:apply-templates>
        <xsl:text>)</xsl:text>
      </xsl:template>
      <xsl:template match="properties" mode="normal">
        <xsl:value-of select="concat(propertySourceName,Operator,$apos,value,$apos)"/>
      </xsl:template>
    </xsl:stylesheet>
    
    

    Usage :

    select xmlcast(
             xmltransform(
               t.xmlcontent
             , <above stylesheet as XMLType>
             )
             as varchar2(4000)
           )
    from sample_data t ;
    
    
This discussion has been closed.