oracle 返回 xml解析,Oracle xmltable解析返回LPX-00209(Oracle xmltable parsing return LPX-00209)...

Oracle xmltable解析返回LPX-00209(Oracle xmltable parsing return LPX-00209)

我是Oracle的新手。 现在我试图解析XML并将其放入VK_ACCOUNTS表中。 我的xml示例在这里：

1656672360

general

1

reports

我用下面的函数请求xml，并且据我看到它正确返回xml：

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;

并获取xml到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;

最后我得到一个错误：

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

我已经试过的是TRIM函数的XML，但它不帮助我。

有任何想法吗？

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:

1656672360

general

1

reports

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?

原文：https://stackoverflow.com/questions/41693602

更新时间：2020-01-21 17:28

相关问答

您不需要多次调用XMLTable，您可以将元素选择移动到列路径子句中，并有三个这样的子句： select x.id1, x.id2, x.id3

from tbl t

cross join

xmltable ('/root'

passing t.xml_data

columns id1 varchar2 (100) path 'element[id=1]/data',

id2 varchar2 (100) path 'element[id=2]/data',

...

1)你的secound sopa请求在cdata中有无效的xml。 SELECT A1.Row1

FROM

xml_tab x,

XMLTable(

XMLNamespaces(

'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV"

,'urn://oracle.bi.webservices/v6' AS "sawsoap"

...

我做了一个解决方法。 XML：

text

SQL： SELECT XML_TABLE.NAME,

DECODE (XML_TABLE.FIRST_ID, NULL, 0, 1) AS HAS_DOCUMENT

FROM XML_DATA,

XMLTABLE('//root' PASSIN

...

做一个选择(或创建视图)是这样的： CREATE VIEW ptcar AS

SELECT Id, LongNameFrench, LongNameDutch,

TO_DATE(x.ValidFromDate, 'YYYY-MM-DD') as ValidFromDate,

...

FROM XML_Infrastructure,

XMLTABLE(

'$d/*:ptcars/*:ptcar'

PASSING XMLTYPE(XML_Infr

...

由于您只有一个父节点CodigoDATA但是有许多子节点PeriodoDATA ，我建议您将查询分成两个不同的：第一个将提取父信息CodigoDATA ，第二个将PeriodoDATA子节点(类似于您正在做的事情)。 Since you just have one parent node CodigoDATA but many child nodes PeriodoDATA, I suggest you split query into 2 different ones: the first wo

...

不确定你为什么看到这种行为; 实现第一个CTE会停止错误，但不会找到任何数据。 不直接相关，但您可以简化查询，至少对于您显示的数据，只需一次XMLTable调用即可： SELECT au.xml_id,

x.tran_ts as chg_timestr,

to_date(substr(x.tran_ts, 0, 8), 'yyyymmdd') chg_date,

cast(substr(x.tran_ts, 9, 6) as int) chg_time,

...

它应该像这样工作 xml_clob := tableau.Get_Clobfromurl(); s := SUBSTR(xml_clob,INSTR(xml_clob,'

您不需要在xml级别上操作以使列具有sysdate： INSERT INTO tbl_employee tbl

(tbl.emp_id

,tbl.name

,tbl.created_on)

SELECT t.emp_id, t.name, sysdate

FROM xmltable('/employee_info'

passing xmldata

columns

...

更改xpath /v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails/*[text()] 至 /v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails

...

您可以通过将steptype xmls与union all交叉连接到主xmls来实现此目的，如下所示： with tab as (

select XMLType(

'

...