Extract XML Values - order by element

3174034 · Apr 21, 2016 3:20AM

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

<propertySourceName>country</propertySourceName>

<value>Austria</value>

</properties>

</properties>

</GroupOperator>

<propertySourceName>generic_name</propertySourceName>

</properties>

<propertySourceName>generic_name1</propertySourceName>

</properties>

</SearchCriteria>'

odie_63 · 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 · Apr 21, 2016 3:57AM

Hi,

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

Paulzip · 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 · 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 · 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 · Apr 21, 2016 5:09AM

No overloading support in xquery? Shame.

odie_63 · 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'

odie_63 · Apr 21, 2016 8:16AM

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 ;

Forum Stats

Quick Links

Categories

Extract XML Values - order by element

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Extract XML Values - order by element

Answers