We help IT Professionals succeed at work.

Parse data from XML node in Oracle table

Gary Antonellis
on
765 Views
Last Modified: 2014-05-05
I have a field in Oracle table with XML data that varies in size and position.  I need to pull out the data from some of these nodes using SQL.

For example in the XML below the node &BADGE_NUM contains the value TEST05.  I would like to write an sql that returns TEST05 in a specific column.  I cannot use SUBSTR because fields prior to BADGE_NUM vary in length so BADGE_NUM is not always in the same position.



dialogresponse.xml?GID=1&DID=11&EVENT_NAME=PRDORD-MTN&MODE=ON&MENU_TYPE=PROMPT&XMLINTERF=Y&BADGE_NUM=TEST05&F_CLOCKTIM=20140414101758&F_ENTITY=10393002&F_OPER=1506&F_QTYCOMP=0&STARTSTOP=S&SBM=M&LAB-T=L&F_SUBCODE3=A&F_SUBCODE4=&CREATOR=MBRESNAH&ORIGREF=&F_ENTITY_T=W&trans_reason=SOLUMINA ADJUSTMENT&F_ROUNDASCLOCKOUT=false&
 Unknown error

Thanks!
Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
that data looks more like a URL (string) so you might be able to approach it like this
    CREATE TABLE TABLE1
    	("ASTRING" varchar2(500))
    ;
    
    INSERT ALL 
    	INTO TABLE1 ("ASTRING")
    		 VALUES ('dialogresponse.xml?GID=1&DID=11&EVENT_NAME=PRDORD-MTN&MODE=ON&MENU_TYPE=PROMPT&XMLINTERF=Y&BADGE_NUM=TEST05&F_CLOCKTIM=20140414101758&F_ENTITY=10393002&F_OPER=1506&F_QTYCOMP=0&STARTSTOP=S&SBM=M&LAB-T=L&F_SUBCODE3=A&F_SUBCODE4=&CREATOR=MBRESNAH&ORIGREF=&F_ENTITY_T=W&trans_reason=SOLUMINA ADJUSTMENT&F_ROUNDASCLOCKOUT=false& Unknown error')
    SELECT * FROM dual
    ;

**Query 1**:

    SELECT
    
        CASE WHEN posright > posleft THEN
                substr(astring, posleft, posright-posleft)
             ELSE NULL
        END as partstring
    
    FROM (
          SELECT
              instr(astring,'=',instr(astring,'BADGE_NUM') ) + 1 AS posleft
            , instr(astring,'&',instr(astring,'BADGE_NUM') )     AS posright
            , astring
          FROM table1
         )
    	
    	
    

**[Results][2]**:
    
    | PARTSTRING |
    |------------|
    |     TEST05 |



  [1]: http://sqlfiddle.com/#!4/cd542/7

Open in new window

nb It uses a subquery to allow posleft and posright to be referenced which also has the advantage of allowing use of a case expression to assist in validation.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gary AntonellisSenior Application Consultant

Author

Commented:
This worked perfectly in Toad but when I moved to clients network and run with SQL Developer it interpreted the ']' as a variable, so it prompted me to enter a value.  

How can I get this to work in SQL Developer?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
try mine :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.