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

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

Oracle XMLTABLE XPath Substring

I have an Oracle query:

SELECT cxm.*
FROM t_mpgm_rule r,
  XMLTABLE(
              '//Criterion'
              PASSING r.rule_xml
              COLUMNS id_attribute PATH '@AttributeID',
                      id_mpgm_rule PATH '@ID'
          ) cxm    

Open in new window


It returns data like this:

ID_ATTRIBUTE       ID_MPGM_RULE
27049                      100030.0
100053                      100058.0
100055                      100058.2

I need to remove the .0 and .2 from the ID_MPGM_RULE.
0
Bob Learned
Asked:
Bob Learned
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Any number of ways.  Main ones that come to mind:   TRUNC and FLOOR

... trunc( ID_MPGM_RULE) ...
0
 
Bob LearnedAuthor Commented:
The type for that column is XMLTYPE, so the type from the PATH '@ID' appears to be a string, since I get an ORA-07122: invalid number trying to use TRUNC or FLOOR.  If I try to cast it to a number, and I get the same error.
0
 
Bob LearnedAuthor Commented:
I see where the problem is.  I thought the data was numeric, too, but there are a few values like this:

100022.4.1
100022.4.3

I used this expression to get the correct value:

SUBSTR(cxm.id_mpgm_rule, 1, INSTR(cxm.id_mpgm_rule, '.') - 1)

Open in new window

0
 
Bob LearnedAuthor Commented:
I figured this one out myself, but with a little help from my friend.  The comment got me to investigate the data in more detail.
0
 
slightwv (䄆 Netminder) Commented:
Guess I went to dinner at the right time!
Glad to be of very little assistance!

Miss crossing paths with you from time to time as a Badger!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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