Issue loading xml data into vertica table

Hi, I'm facing an issue loading xml data into vertica table using External Filter using the below command:

COPY cities FROM LOCAL ‘sample_data.xml’ WITH FILTER ExternalFilter(‘/path/to/xmlparser.py’);

 The sample_data.xml contains:



<city>Cambridge, MA</city>





“”” xmlparser.py:

#!/usr/bin/env python

import sys, xml.etree.ElementTree

for record in xml.etree.ElementTree.fromstringlist(sys.stdin).getchildren():

keys = record.getchildren()

print ‘|’.join(key.text for key in keys)


 The xml parser is working fine but while loading the data using COPY command, I am getting an error - ERROR 4856: Syntax error at or near "'xmlparser.py'" at character 69 Please advise.

 Please refer to this link for External Filter and python code(for xml parser). http://www.vertica.com/2012/11/14/how-to-parse-anything-into-vertica-using-externalfilter/


  • Options
    Hi Deepthi, 

    Can you try COPY command as below and see if it helps?

    COPY cities FROM LOCAL ‘sample_data.xml’ WITH FILTER ExternalFilter(cmd='python /path/to/xmlparser.py’); 


  • Options

    Do you installed External Filter from shell_load_package? 99,99% - its not installed.
    Install and everything will work.

    If it installed, so check quotes in query:
    • good single quote is apostrophe - <'> (U+0027 APOSTROPHE)
    • bad single quotes <‘> and <’> (U+2019 RIGHT SINGLE QUOTATION MARK)
  • Options
    Hi Pravesh,

    I tried using your approach but I'm still getting an error. Please see below:

    dbadmin=> COPY cities_xml FROM LOCAL 'sample1.xml' WITH FILTER ExternalFilter(cmd='python /home/dbadmin/xmlparser.py');

    ERROR 3399:  Failure in UDx RPC call InvokeProcessUDL(): Error calling processUDL() in User Defined Object [ExternalFilter] at [ProcessLaunchingPlugin.cpp:82], error code: 0, message: External process 'python /home/dbadmin/xmlparser.py' reported error: python: can't open file '/home/dbadmin/xmlparser.py': [Errno 13] Permission denied

  • Options

    Use in search: shell_load_package permission error.
    Same solved issue.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file