Skip to content
Advertisement

Extracting values from XML column in Oracle

I have some data in an Oracle table which is stored in an XML-format string (column response in table WS_LOG).

I would like to extract data from each different node below <MedicalProcedureOutput>, but I’m having some difficulties in getting to each of the nodes. Can you spot what I’m doing wrong?

Here’s what I’m trying (here trying to retrieve the value for the icpcID tag):

And here’s some example data

I would expect to be getting the value ‘011801’. Please note that multiple <MedicalProcedureOutput> nodes may occur, and they would be organized as follows:

Advertisement

Answer

You need to declare a default namespace for everything from within the body, since createBillingSubmissionForAFEBSGResponse has its own xmlns unnamed (therefore default) declaration which applies from that node onwards; so:

or with multiple nodes as you showed later int he question this will return:

db<>fiddle

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement