Loading data using XML file

[chethanm@hp.com]

I am new to vertica. we would like to use loading data using XML file . can any one explain how we can do that?

Whether copy command supports that?. what are the settings or format we need to follow.

Thanks a Lot for answers.

[id10t]

HI!

1. Vertica provides three different programming interfaces—user defined functions, user defined load functions and external procedures—which can handle complex parsing tasks that are hard to resolve using regex.

2. https://github.com/vertica/Vertica-Exte ... ml_package

no native build in option.

PS
>> Whether copy command supports that?
You have no Vertica docs?

[chethanm@hp.com]

really not able to follow any of the above suggestions. Needs one example from you.

I have tried laoding the xml generated by Toda for anlayitics tool.

Just created table.

1. create table t1(emp number)

2. insert t1(emp) values (100)

3. select * from t1;

4. generated the output 100.

5. export the out put to xml data.

6. genarated the xml as follows.


- <DataTable>
- <t1>
<emp>100</emp>
</t1>
</DataTable>

7. if i try to load the same from copy command through toad Analytics into vertica.

Copy 't1' from local 'C:\load1.xml'

It gives an error.

Vertics docs talks lot of theorey not even a simple example.

Can you help with this? whether Use defined fucntion or source how to use it any example.. i saw one guy shoing the laoding webcam data laoding.. even that also incomplete..?



Regards,

[JimKnicely]

Hi chethanm,

As sKwa indicated, unfortunately we can not directly load an XML file via the COPY command. The COPY command is looking for some sort of delimited file (i.e. comma, tab, pipe (the default), etc.).

If you have MS Excel, you can load the XML file into it and then save it as a CSV file and then use the COPY command to load that file.

There are several xml to csv converters you can download off the net, i.e. from http://xmltocsv.codeplex.com/. Just google "xml to csv".

These are just several suggestions in addition to sKwa's.

Thanks!

[jpcavanaugh]

Another option is to use the ExternalSource UDL from github. You can then use whatever command line interface you want for converting the XML.

Code: Select all

 ... WITH SOURCE ExternalSource(cmd="xsltproc stylesheet.xslt data.xml"); ...

[doug_harmon]

The Vertica blog recently posted an example.

http://www.vertica.com/2012/11/14/how-t ... nalfilter/

[id10t]

Hi doug!

Thanx for sharing. Did you tested performance of ExternalFilter?