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>
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;
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
