[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Selecting Multiple Values From Oracle XMLTYPE Column

Posted on 2014-10-06
9
Medium Priority
?
3,779 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 78

Assisted Solution

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

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 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 800 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

591 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