python解析xml入库_在python中解析XML并将其添加到数据库中

考虑使用Oracle的XML处理程序并避免任何嵌套循环：SELECT e.SysName, e.ServerDt, e."UID", e.PersID, e.Emp_name, e.EventID, e.EventTXT,

e.CauseEventID, e.CauseEventTXT, e.EventBegda, e.EventEndda,

e.AccrualsSum, e.AccrualsProz, e.OrderNum, e.Perg, e.Perk, e.Awart

FROM XMLTABLE('/Root/SvcNf/PersonNf/PersonList/Row'

PASSING XMLTYPE('

MI6

2016-10-28 03:00:12 +03:00

9457A55E17341AA7ASDEDS057A8BFFF3

007

James Bond

25

Drinking alcohol

03

Martini with vodka

2017-10-18

2017-10-18

171.0

0.0

Chinees_

P-336

0

15

')

COLUMNS

SysName VARCHAR2(25) PATH 'SysName',

ServerDt VARCHAR2(25) PATH 'ServerDt',

"UID" VARCHAR2(25) PATH 'UID',

PersID VARCHAR2(25) PATH 'PersID',

Emp_name VARCHAR2(25) PATH 'Emp_name',

EventID NUMBER PATH 'EventID',

EventTXT VARCHAR2(25) PATH 'EventTXT',

CauseEventID VARCHAR2(25) PATH 'CauseEventID',

CauseEventTXT VARCHAR2(25) PATH 'CauseEventTXT',

EventBegda VARCHAR2(25) PATH 'EventBegda',

EventEndda VARCHAR2(25) PATH 'EventEndda',

AccrualsSum VARCHAR2(25) PATH 'AccrualsSum',

AccrualsProz VARCHAR2(25) PATH 'AccrualsProz',

OrderNum VARCHAR2(25) PATH 'OrderNum',

Perg NUMBER PATH 'Perg',

Perk NUMBER PATH 'Perk',

Awart VARCHAR2(25) PATH 'Awart') AS e;

要在Python数据库游标(如with cx_Oracle)中实现追加查询：

^{pr2}$

对于在SQL中超过Oracle 4000字节的非常大的XML内容，请使用varchar2限制为32767字节的PL/SQL，如@NickS所示here：sql = """

DECLARE

xml_value varchar2(32767);

BEGIN

xml_value := :i_param;

INSERT INTO mytable (Col1, Col2, Col3, ...)

SELECT ...same as above...

FROM XMLTABLE('/Root/SvcNf/PersonNf/PersonList/Row'

PASSING XMLTYPE(xml_value)

COLUMNS

...same as above...) AS e

commit;

END;

"""

# PARSE XML FILE

doc = ET.parse(events)

xmlstr = ET.tostring(doc.getroot()).decode('utf-8')

# PASS XML STRING AS PARAMETER

cur.execute(sql, {'i_param':xmlstr})

dbconn.commit()