Solved

Selecting Multiple Values From Oracle XMLTYPE Column

Posted on 2014-10-06
9
1,971 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 76

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 76

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 73

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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now