Solved

Selecting Multiple Values From Oracle XMLTYPE Column

Posted on 2014-10-06
9
2,270 Views
Last Modified: 2014-10-06
I have the following value from an Oracle 11g XMLTYPE column:

"<RuleXml ID="100023">
   <Criterion ID="100023.0" AttributeID="100053" Comparison="EQ" AudienceCount="0" AudienceCountTime="0001-01-01T00:00:00" Value="fr-CA"/>
   <Operator ID="100023.1" Value="AND"/>
   <Criterion ID="100023.2" AttributeID="100068" Comparison="NI">
      <Value>CA5_OFFR_1</Value>
      <Value>CA5_OFFR_2</Value>
      <Value>CA5_OFFR_3</Value>
      <Value>CA7_PO_1</Value>
      <Value>CA7_IP_1</Value>
      <Value>CA7_IPF_1</Value>
      <Value>CA7_IG_1</Value>
      <Value>CA7_PO_2</Value>
      <Value>CA7_IP_2</Value>
      <Value>CA7_IPF_2</Value>
      <Value>CA7_IG_2</Value>
      <Value>CA7_PO_3</Value>
      <Value>CA7_IP_3</Value>
      <Value>CA7_IPF_3</Value>
      <Value>CA7_IG_3</Value>
   </Criterion>
</RuleXml>"

I need to use something like EXTRACTVALUE function, that can return multiple attribute values.
0
Comment
Question by:Bob Learned
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 40364617
EXTRACTVALUE is deprecated.  XMLTABLE is the new thing.

What is the expected results from that XML?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 40364625
If just the two criterion attribute ID's,
try this:
with mydata as (
select xmltype(
'<RuleXml ID="100023">
    <Criterion ID="100023.0" AttributeID="100053" Comparison="EQ" AudienceCount="0" AudienceCountTime="0001-01-01T00:00:00" Value="fr-CA"/>
    <Operator ID="100023.1" Value="AND"/>
    <Criterion ID="100023.2" AttributeID="100068" Comparison="NI">
       <Value>CA5_OFFR_1</Value>
       <Value>CA5_OFFR_2</Value>
       <Value>CA5_OFFR_3</Value>
       <Value>CA7_PO_1</Value>
       <Value>CA7_IP_1</Value>
       <Value>CA7_IPF_1</Value>
       <Value>CA7_IG_1</Value>
       <Value>CA7_PO_2</Value>
       <Value>CA7_IP_2</Value>
       <Value>CA7_IPF_2</Value>
       <Value>CA7_IG_2</Value>
       <Value>CA7_PO_3</Value>
       <Value>CA7_IP_3</Value>
       <Value>CA7_IPF_3</Value>
       <Value>CA7_IG_3</Value>
    </Criterion>
 </RuleXml>') myxml 
  from dual
)
select attribute_id from mydata,
			XMLTable('/RuleXml'
   				PASSING mydata.myxml
				COLUMNS
					criterion xmltype PATH 'Criterion'
			) criterionxml,
			XMLTable('/Criterion'
   				PASSING criterion
				COLUMNS
					attribute_id xmltype PATH '@AttributeID'
			) criterionxml
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 40364661
I'm going to take a guess that you're trying to resolve "Value" as an attribute but also "Value" when it is a child node.

If so, try something like this...


 SELECT c.id,
                  c.attributeid,
                  c.comparison,
                  c.audiencecount,
                  c.audiencecounttime,
                  COALESCE(c.value_attr, v.value_child) value
             FROM yourtable,
                  XMLTABLE(
                      '//Criterion'
                      PASSING yourxml
                      COLUMNS id PATH './@ID',
                              attributeid PATH './@AttributeID',
                              comparison PATH './@Comparison',
                              audiencecount PATH './@AudienceCount',
                              audiencecounttime PATH './@AudienceCountTime',
                              value_attr PATH './@Value',
                              self XMLTYPE PATH '.'
                  ) c,
                  XMLTABLE('//Value' PASSING c.self COLUMNS value_child PATH 'text()')(+) v
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 96

Author Comment

by:Bob Learned
ID: 40364776
The table is T_MPGM_RULE and the column is rule_xml

AttributeID="100053"
AttributeID="100068"

The expected result is:

100053
100068
0
 
LVL 96

Author Comment

by:Bob Learned
ID: 40364821
Here is what I came up with:

SELECT DISTINCT rx.attributeid attribute_id
    FROM t_mpgm_rule r, 
          XMLTABLE(
              '//Criterion'
              PASSING rule_xml
              COLUMNS attributeid PATH './@AttributeID'
          ) rx

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364827
if that's all you need then that should be sufficient and effcient
0
 
LVL 96

Author Closing Comment

by:Bob Learned
ID: 40364829
Thank you for the heads up on XMLTABLE.  I struggled with that one for about 2 hours without success.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40364840
Technically the "./" shouldn't be needed.  I alternate back and forth on using it or not.
0
 
LVL 96

Author Comment

by:Bob Learned
ID: 40364870
Yes, I see where './' wasn't necessary, but I left it in.  I was able to inner join with the attribute table to get the attribute names that are used with the rules, so I am happy :D
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SubQuery link 4 36
query question 12 32
UTL_FILE invalid file operation 5 29
sql server store procedure contains temp tables need to convert oracle? 3 28
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question