Oracle xmltable parsing return LPX-00209

Question

i'm newbie in Oracle. Right now i'm trying to parse XML and put it inside VK_ACCOUNTS table. My xml sample is here:

<?xml version="1.0" encoding="utf-8"?>
<response list="true">
 <account>
  <account_id>1656672360</account_id>
  <account_type>general</account_type>
  <account_status>1</account_status>
  <access_role>reports</access_role>
 </account>
</response>

I request xml with function below and as far as i see it returns xml properly:

create or replace function GET_CLOBFROMURL(
  p_url     varchar2,
  p_charset varchar2 default 'UTF8'
) return clob
is
  req   utl_http.req; 
  resp  utl_http.resp;
  val   varchar2(32547);
  a     clob;
BEGIN
  a:='';
  dbms_lob.createtemporary(a,true);
  dbms_lob.open(a,dbms_lob.lob_readwrite);
  req := utl_http.begin_request(p_url);
  utl_http.set_body_charset(req, p_charset);
  resp := utl_http.get_response(req);
  LOOP
    a := a||val;
    utl_http.read_text(resp, val, 5000);
  END LOOP;
  utl_http.end_response(resp);
  return a;
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
    return a;
  WHEN others then
    utl_http.end_response(resp);
    raise;
END;

And get xml to xml_clob

xml_clob := tableau.get_clobFromUrl(REQUEST);
    IF xml_clob != EMPTY_CLOB() THEN
    insert into tableau.VK_ACCOUNTS(account_id, account_type, account_status, access_role)
        SELECT
        proc.account_id,
        proc.account_type,
        proc.account_status,
        proc.access_role   
        FROM XMLTABLE('response/account' passing (select xmltype(xml_clob)  resp FROM dual)
            columns account_id number             path '/account/account_id',
                    account_type varchar2(20)     path '/account/account_type',
                    account_status number         path '/account/account_status',
                    access_role varchar2(20)      path '/account/access_role'
                ) proc;
      COMMIT;     
    END IF;
END;

Finally i get an error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML
LPX-00209: PI names starting with XML are reserved
Error at line 3
ORA-06512: на  "SYS.XMLTYPE", line 272
ORA-06512: на  line 1
ORA-06512: на  "TABLEAU.VK_LOADDATA", line 11
ORA-06512: на  line 2

What i have already tried is TRIM function for XML but it doesn't help me.

Any ideas?

Answer 1

it should work like this

xml_clob := tableau.Get_Clobfromurl(); s := SUBSTR(xml_clob,INSTR(xml_clob,'<'));

Answer 2

I know 3 ways to repeat this exception.

1. Prolog exist more than once in xml.

select xmltype('<?xml version="1.0" ?><?xml version="1.0" ?><a></a>') from dual;

2. Prolog isn't first element of xml. .

select xmltype('<a></a><?xml version="1.0" ?>') from dual;

3. The same situation but here fist line of xml is empty.

select xmltype(chr(10)||chr(13)||'<?xml version="1.0" ?><a></a>') from dual;

I think you are facing with problem no. 3. Fast way to get rid of empty first line. Is less known form of function TRIM.

select xmltype( trim(leading chr(13) from trim(leading chr(10) from trim(' '||chr(10)||chr(13)||' <?xml version="1.0" ?><a></a>')))) from dual;

trim(leading chr(13) from trim(leading chr(10) from trim('your_xml_clob')))

Answer 3

LPX-00209: PI names starting with XML are reserved

Appears to happen when there is leading whitespace characters in the XML string. See http://oraclequirks.blogspot.co.uk/2013/03/lpx-00209-pi-names-starting-with-xml.html.

You should be able to solve it by using:

XMLType( TRIM( xml_clob ) )

Simplifying your function slightly:

create or replace function GET_CLOBFROMURL(
  p_url     varchar2,
  p_charset varchar2 default 'UTF8'
) return clob
is
  req   utl_http.req; 
  resp  utl_http.resp;
  val   varchar2(32547);
  a     clob    := EMPTY_CLOB();
BEGIN
  req := utl_http.begin_request(p_url);
  utl_http.set_body_charset(req, p_charset);
  resp := utl_http.get_response(req);
  LOOP
    BEGIN
      utl_http.read_text(resp, val, 5000);
      a := a||val;
    EXCEPTION
      WHEN utl_http.end_of_body THEN
        utl_http.end_response(resp);
        EXIT;
    END;
  END LOOP;
  return a;
EXCEPTION
  WHEN others THEN
    utl_http.end_response(resp);
    RAISE;
END;

Then the insert can be done entirely in SQL:

insert into tableau.VK_ACCOUNTS (
  account_id,
  account_type,
  account_status,
  access_role
)
SELECT proc.account_id,
       proc.account_type,
       proc.account_status,
       proc.access_role   
FROM   ( SELECT tableau.get_clobFromUrl(REQUEST) AS xml FROM DUAL ) c,
       XMLTABLE(
         '/response/account'
          passing XMLType( TRIM( c.xml ) )
          columns account_id     number       path './account_id',
                  account_type   varchar2(20) path './account_type',
                  account_status number       path './account_status',
                  access_role    varchar2(20) path './access_role'
            ) proc
WHERE  c.xml IS NOT NULL
AND    DBMS_LOB.GETLENGTH( c.xml ) > 0;