Extract Value from XML file-CodePudding

I am trying to get the values from an XML file, like below response. I want to extract the value: 289

<operationExecutionResponse>
  <responseCode>0</responseCode>
  <rootContext>
    <stringList name="productIds">
      <string>289</string>
      <string>123</string>
    </stringList>
  </rootContext>
</operationExecutionResponse>

I tried something like this:

SELECT EXTRACTVALUE (
r_val,
'//operationExecutionResponse/rootContex/stringList/string[1]')
INTO n_id
FROM DUAL;

SELECT EXTRACTVALUE (
r_val,
'//operationExecutionResponse/rootContex/stringList/string[@name="productIds"]/text()')
INTO n_id
FROM DUAL;

How can I get the value I want?

CodePudding user response：

Try this:

WITH XML_TBL 
AS  (SELECT XMLTYPE('<operationExecutionResponse>
                      <responseCode>0</responseCode>
                      <rootContext>
                        <stringList name="productIds">
                          <string>289</string>
                          <string>123</string>
                        </stringList>
                      </rootContext>
                    </operationExecutionResponse>') XMLDATA
       FROM DUAL)
SELECT VALS
  FROM XML_TBL
     , XMLTABLE(
                  'operationExecutionResponse/rootContext/stringList//*'
                  PASSING XML_TBL.XMLDATA 
                  COLUMNS VALS VARCHAR2(100) PATH 'text()'
                );

CodePudding user response：

Your first attempt is almost right, but you spelled rootContext wrong - it's missing the final t. With that corrected that works. (Your second one has the attribute name filter on the wrong node, and would try to return all values not just the first one.)

But extractvalue() is deprecated. You can do the same thing with XMLQuery():

select XMLQuery(
    '/operationExecutionResponse/rootContext/stringList/string[1]/text()'
    passing r_val
    returning content
  ).getstringval()
into n_id
from dual;

or as you seem to be expecting a number:

select to_number(
    XMLQuery(
      '/operationExecutionResponse/rootContext/stringList/string[1]/text()'
      passing r_val
      returning content
    ).getstringval()
  )
into n_id
from dual;

db<>fiddle

In both cases that will only get the first string/number from the list, which is what your original code did. If you want to get all values you can use XMLTable() as @Gnqz showed, but (as you're doing this in a PL/SQL context) you'll need to either select those into a collection or turn it into a cursor query and loop over them - depends what you plan to do with the values once you have them.

As a cursor you might do:

for r in (
  select n_id
  from XMLTable(
      '/operationExecutionResponse/rootContext/stringList[@name="productIds"]/string'
    passing r_val
    columns n_id number path '.'
  )
)
loop
  -- do something with the value, as r.n_id here
  dbms_output.put_line('Number: ' || r.n_id);
end loop;

db<>fiddle