asked on

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.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

ASKER

The table is T_MPGM_RULE and the column is rule_xml

AttributeID="100053"
AttributeID="100068"

The expected result is:

100053
100068

Bob Learned

ASKER

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

Sean Stuber

if that's all you need then that should be sufficient and effcient

Bob Learned

ASKER

Thank you for the heads up on XMLTABLE. I struggled with that one for about 2 hours without success.

Sean Stuber

Technically the "./" shouldn't be needed. I alternate back and forth on using it or not.

Bob Learned

ASKER

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