Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Extract XML Values - order by element

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>'
Answers
-
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.
-
Hi,
As of now, we dont use nested <GroupOperator>. Many Thanks.
-
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.
-
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, "'", $p/value, "'") 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'
-
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 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, "'", $i/value, "'") 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, "'", $i/value, "'") 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'
-
And here's the XSLT way :
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:variable name="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 ;