Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priyanka.

Asked: June 27, 2018 - 10:40 am UTC

Last updated: February 25, 2021 - 5:39 am UTC

Version: 12.2.0.1.0

Viewed 10K+ times! This question is

You Asked

While extracting data from xml parsing issue is coming. Issue is coming while extracting data for field_name 401K_LOAN_1 and 401K_LOAN_2. Please advise.

PROCEDURE SP1(
    SXML IN CLOB,
    p_status OUT VARCHAR2,
    p_message OUT VARCHAR2 )
AS
  v_sql_err_code CCL_HCM.HCM_SYSTEM_ERROR_LOG.ERROR_CODE%TYPE;
  v_sql_err_msg CCL_HCM.HCM_SYSTEM_ERROR_LOG.ERROR_MSG%TYPE;
  XMLRECORDSET SYS.XMLTYPE;
BEGIN
  XMLRECORDSET     := SYS.XMLTYPE.CREATEXML (SXML);
  p_status         := 'Y';
  p_message        := '';
  For Xmlrecordrow In
  (Select
xmlRecordRow.EXTRACT( '//PERSON_NUMBER/text()' ).getstringval ()    AS X_PERSON_NUMBER,
Xmlrecordrow.Extract( '//' || '"401K_LOAN_1"' || '/text()' ).Getstringval ()    As X_401K_LOAN_1
xmlRecordRow.EXTRACT( '//401K_LOAN_2/text()' ).getstringval ()    AS X_401K_LOAN_2,
  FROM TABLE (XMLSEQUENCE (XMLRECORDSET.EXTRACT ('//ROWSET/ROW'))) XMLRECORDROW
  )
  LOOP
    INSERT
    INTO CCL_HCM.ASSIGNMENT_STAGING
      (
--table columns listed here
)
      VALUES
      (
HCM_UTILITIES_API.FN_REPLACE_XMLSPECIALCHARS(XMLRECORDROW.X_PERSON_NUMBER),
HCM_UTILITIES_API.FN_REPLACE_XMLSPECIALCHARS(XMLRECORDROW.X_401K_LOAN_1),
HCM_UTILITIES_API.FN_REPLACE_XMLSPECIALCHARS(XMLRECORDROW.X_401K_LOAN_2)
)
);
  END LOOP;
  -- Do not Commit untill all the transactions completed
  COMMIT;
END SP1;


you may use following xml for testing:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<401K_LOAN_1>25000</401K_LOAN_1>
<401K_LOAN_2>26000</401K_LOAN_2>
</ROW>
</ROWSET>

and Chris said...

You have invalid XML!

Element names must start with a letter or underscore. So you need to change the 401K tags.

select xmltype('<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<401K_LOAN_1>25000</401K_LOAN_1>
<401K_LOAN_2>26000</401K_LOAN_2>
</ROW>
</ROWSET>').extract( '//PERSON_NUMBER/text()' ).getstringval() p#
from dual;

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 52 ('4') found in a Name or Nmtoken
Error at line 5
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1

select xmltype('<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
<LOAN_2>26000</LOAN_2>
</ROW>
</ROWSET>').extract( '//PERSON_NUMBER/text()' ).getstringval() p#
from dual;

P#                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------
1000142

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

XML Tag elements which starts with Number cannot be parsed

Srinivasa Kumar Challa, May 30, 2019 - 11:40 am UTC

XML Tag elements which starts with Number cannot be parsed

Is there any work around to convert the Elements which starts with Number to Text or how to handle this
Chris Saxon
June 03, 2019 - 12:11 pm UTC

What exactly is your XML and how are you trying to parse it?

How to handle such exception ?

Jaroslav, February 24, 2021 - 4:13 pm UTC

Hello is there any way, how to catch and handle this exception (ORA-31011: XML parsing failed) ? For example in loop

for x in (select someblob from sometable ) loop
begin
y:=xmltype(x.someblob);
exception when <...>
...
end;
end loop;

what to put in place of <...> ?
Thanks

Connor McDonald
February 25, 2021 - 5:39 am UTC

Any error message for which you know the number can be converted to a named exception in your code, eg

SQL> declare
  2    x xmltype;
  3  begin
  4      x := xmltype('some junk');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '<' instead of 's'
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 4

SQL> set serverout on
SQL> declare
  2    x xmltype;
  3    invalid_xml exception;
  4    pragma exception_init(invalid_xml,-31011);
  5  begin
  6      x := xmltype('some junk');
  7  exception
  8    when invalid_xml then
  9       dbms_output.put_line('This did not work');
 10  end;
 11  /
This did not work

PL/SQL procedure successfully completed.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.