Oracle解析XML字符串

Dear200892 2021-02-23 06:01:40

只熟悉SQLServer。
需要将v_EventDataXml字符串解析，并且保存到临时表中。
批量执行后，提示：在此 SELECT 语句中缺少 INTO 子句

哪里出错了？？？



create global temporary table TPECOVID19Info  

(

  PhysicalRegisterId number(10,0),

  SourceCodeId number(10,0),

  IdentityCodeId number(10,0),

  IsFromForeignin14 number(10,0),

  CreateEmployeeId number(10,0),

  CreateTime TIMESTAMP(6)

) on commit preserve rows;

--drop table TPECOVID19Info;

DECLARE

  v_EventDataXml clob;

  v_Xml xmltype;

  v_PhysicalRegisterId number(10,0);

  v_SourceCodeId number(10,0);

  v_IdentityCodeId number(10,0);

  v_IsFromForeignin14 number(10,0);

  v_CreateEmployeeId number(10,0);

  CreateTime TIMESTAMP(6);

BEGIN

   v_EventDataXml := '<PECOVID19Info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<PhysicalRegisterId>21034</PhysicalRegisterId><SourceCodeId>2</SourceCodeId><IdentityCodeId>1</IdentityCodeId><IsFromForeignin14>1</IsFromForeignin14>

<CreateEmployeeId>9062</CreateEmployeeId><CreateTime>2021-02-23T16:34:57.9812563+08:00</CreateTime></PECOVID19Info>';

  v_Xml:=xmltype(v_EventDataXml);

  

   --insert into TPECOVID19Info(v_PhysicalRegisterId,v_SourceCodeId,v_IdentityCodeId,v_IsFromForeignin14,v_CreateEmployeeId)   

   select PhysicalRegisterId ,SourceCodeId ,IdentityCodeId,IsFromForeignin14,CreateEmployeeId

   into v_PhysicalRegisterId,v_SourceCodeId,v_IdentityCodeId,v_IsFromForeignin14,v_CreateEmployeeId

   from xmltable('PECOVID19Info' 

                passing v_Xml

                columns PhysicalRegisterId number(10,0) path 'PhysicalRegisterId',

                        SourceCodeId number(10,0) path 'SourceCodeId',

                        IdentityCodeId number(10,0) path 'IdentityCodeId',

                        IsFromForeignin14 number(10,0) path 'IsFromForeignin14',

                        CreateEmployeeId number(10,0) path 'CreateEmployeeId'

                ) t;

   --dbms_output.put_line(v_EventDataXml);

  select * from TPECOVID19Info ;

   EXCEPTION

      WHEN OTHERS THEN

        dbms_output.put_line('执行出错了，老板！');

END;