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
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!
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.