Gary Antonellis
asked on
Parse data from XML node in Oracle table
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&D ID=11&EVEN T_NAME=PRD ORD-MTN&MO DE=ON&MENU _TYPE=PROM PT&XMLINTE RF=Y&BADGE _NUM=TEST0 5&F_CLOCKT IM=2014041 4101758&F_ ENTITY=103 93002&F_OP ER=1506&F_ QTYCOMP=0& STARTSTOP= S&SBM=M&LA B-T=L&F_SU BCODE3=A&F _SUBCODE4= &CREATOR=M BRESNAH&OR IGREF=&F_E NTITY_T=W& trans_reas on=SOLUMIN A ADJUSTMENT&F_ROUNDASCLOCKO UT=false&
Unknown error
Thanks!
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&D
Unknown error
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
How can I get this to work in SQL Developer?
try mine :)
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.