Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3324
  • Last Modified:

Selecting Multiple Values From Oracle XMLTYPE Column

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
Bob Learned
Asked:
Bob Learned
  • 4
  • 3
  • 2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
EXTRACTVALUE is deprecated.  XMLTABLE is the new thing.

What is the expected results from that XML?
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Bob LearnedAuthor Commented:
The table is T_MPGM_RULE and the column is rule_xml

AttributeID="100053"
AttributeID="100068"

The expected result is:

100053
100068
0
 
Bob LearnedAuthor Commented:
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
 
sdstuberCommented:
if that's all you need then that should be sufficient and effcient
0
 
Bob LearnedAuthor Commented:
Thank you for the heads up on XMLTABLE.  I struggled with that one for about 2 hours without success.
0
 
sdstuberCommented:
Technically the "./" shouldn't be needed.  I alternate back and forth on using it or not.
0
 
Bob LearnedAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now