How to parse xml in pl-sql?

问题:

I have a string that contains xml and dummy texts together as follows.

I need to read value Hello world and 999.

Can you help please?

[GCM] Dummy text1
[GCM] Dummy text2

<PARAMETER_LIST>
  <PARAMETER>
    <NAME>SMS</NAME> 
    <VALUE>Hello world</VALUE>
  </PARAMETER>
  <PARAMETER>
    <NAME>ERROR_CODE</NAME>
    <VALUE>999</VALUE>
  </PARAMETER> 
</PARAMETER_LIST> 

回答1:

Also, you might wanna use XMLTABLE:

SELECT
  px.*
FROM
  XMLTABLE(
    '/PARAMETER_LIST/PARAMETER' PASSING 
      XMLTYPE(
        REGEXP_REPLACE('
              [GCM] Dummy text1
              [GCM] Dummy text2
              <PARAMETER_LIST>
                <PARAMETER>
                  <NAME>SMS</NAME> 
                  <VALUE>Hello world</VALUE>
                </PARAMETER>
                <PARAMETER>
                  <NAME>ERROR_CODE</NAME>
                  <VALUE>999</VALUE>
                </PARAMETER> 
              </PARAMETER_LIST>'
              ,'^[^<]+'
              ,''
              )
        )
    COLUMNS param_id FOR ORDINALITY
    ,name VARCHAR2(40) PATH 'NAME'
    ,value VARCHAR2(400) PATH 'VALUE'
  ) px;

This would give you the result:

  PARAM_ID  |    NAME      |    VALUE
------------------------------------------
   1        |    SMS       |  Hello world
   2        |  ERROR_CODE  |   999

Also, a bit tuned version of Shaun Peterson's solution (without switching PLSQL - SQL context):

declare

MY_STRING VARCHAR2(4000) := '[GCM] DUMMY TEXT1 [GCM] DUMMY TEXT2 <PARAMETER_LIST><PARAMETER><NAME>SMS</NAME><VALUE>Hellow world</VALUE></PARAMETER><PARAMETER><NAME>ERROR_CODE</NAME><VALUE>999</VALUE></PARAMETER></PARAMETER_LIST>';
MY_XML XMLTYPE;

MY_SMS VARCHAR2(100);
MY_ERROR_CODE VARCHAR2(100); 

begin

MY_XML := XMLTYPE(SUBSTR(MY_STRING, INSTR(MY_STRING, '<'))) ;
MY_SMS := MY_XML.EXTRACT('/PARAMETER_LIST/PARAMETER[NAME="SMS"]/VALUE/text()').getstringval;
MY_ERROR_CODE := MY_XML.EXTRACT('/PARAMETER_LIST/PARAMETER[NAME="ERROR_CODE"]/VALUE/text()').getstringval;

DBMS_OUTPUT.PUT_LINE('MY_SMS = ' || MY_SMS);
DBMS_OUTPUT.PUT_LINE('MY_ERROR_CODE = ' || MY_ERROR_CODE);

end;

回答2:

Hi I would extract the xml from this string, put it in a xml type and then process from there. See below for an example.

declare

MY_STRING VARCHAR2(4000) := '[GCM] DUMMY TEXT1 [GCM] DUMMY TEXT2 <PARAMETER_LIST><PARAMETER><NAME>SMS</NAME><VALUE>Hellow world</VALUE></PARAMETER><PARAMETER><NAME>ERROR_CODE</NAME><VALUE>999</VALUE></PARAMETER></PARAMETER_LIST>';
MY_XML XMLTYPE;

MY_SMS VARCHAR2(100);
MY_ERROR_CODE VARCHAR2(100); 

begin

SELECT XMLTYPE(SUBSTR(MY_STRING, INSTR(MY_STRING, '<'))) 
INTO MY_XML
FROM DUAL;

SELECT EXTRACTVALUE(MY_XML, '/PARAMETER_LIST/PARAMETER[NAME="SMS"]/VALUE'),
       EXTRACTVALUE(MY_XML, '/PARAMETER_LIST/PARAMETER[NAME="ERROR_CODE"]/VALUE')
INTO MY_SMS, MY_ERROR_CODE
FROM DUAL;

DBMS_OUTPUT.PUT_LINE('MY_SMS = ' || MY_SMS);
DBMS_OUTPUT.PUT_LINE('MY_ERROR_CODE = ' || MY_ERROR_CODE);

end;

This will give you the output

MY_SMS = Hellow World

MY_ERROR_CODE = 999