Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting Multiple Values From Oracle XMLTYPE Column

Posted on 2014-10-06
9
Medium Priority
?
3,074 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

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 77

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

661 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