Postgres Pro
Downloads
Home   >   mailing lists

Thread: Copy and xml files

Copy and xml files

From
"C G"
Date:
Dear All,

I'm trying to insert an xml file into my database. I have a table with a
single text column. My intention is just to have the xml file take up one
row in the table.

I've tried the command

COPY t1 FROM '/tmp/file.xml';

but keep getting the error message

ERROR:  extra data after last expected column
CONTEXT:  COPY t1, line 32: "           <cn type="integer"> 1 </cn>"

Thanks for any help

Colin

_________________________________________________________________
Sign-up for a FREE BT Broadband connection today!
http://www.msn.co.uk/specials/btbroadband

Re: Copy and xml files

From
Chris Gamache
Date:
COPY works for whole tables, and can't append rows. You're going to have to
escape your linebreaks and tabs to use COPY ...  As I recall, you can specify
your own custom column and row delimiters... You might consider creating some
type of primary key for the table. A "serial" column would work fine.

If you want to insert a single row into a table, you'll need to do it with an
insert statement. something like :

#  psql your_database -c "insert into t1 (xml_column) values ('`cat
/tmp/file.xml |  sed -e \"s/'/''/g\"`');"


HTH,

CG

--- C G <csgcsg39@hotmail.com> wrote:
> Dear All,
>
> I'm trying to insert an xml file into my database. I have a table with a
> single text column. My intention is just to have the xml file take up one
> row in the table.
>
> I've tried the command
>
> COPY t1 FROM '/tmp/file.xml';
>
> but keep getting the error message
>
> ERROR:  extra data after last expected column
> CONTEXT:  COPY t1, line 32: "           <cn type="integer"> 1 </cn>"
>
> Thanks for any help
>
> Colin
>
> _________________________________________________________________
> Sign-up for a FREE BT Broadband connection today!
> http://www.msn.co.uk/specials/btbroadband
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Re: Copy and xml files

From
Guy Fraser
Date:
Chris Gamache wrote:

>COPY works for whole tables, and can't append rows. ...
>
>
Wrong!

I use copy regularly to add data to a table.


...snip...

>--- C G <csgcsg39@hotmail.com> wrote:
>
>
>>Dear All,
>>
>>I'm trying to insert an xml file into my database. I have a table with a
>>single text column. My intention is just to have the xml file take up one
>>row in the table.
>>
>>I've tried the command
>>
>>COPY t1 FROM '/tmp/file.xml';
>>
>>but keep getting the error message
>>
>>ERROR:  extra data after last expected column
>>CONTEXT:  COPY t1, line 32: "           <cn type="integer"> 1 </cn>"
>>
>>Thanks for any help
>>
>>Colin
>>
>>
If you are trying to import the whole file into one 'column' of a table, you will need to escape the "end of line"
[carriagereturn/line feed] characters. 

I believe this question has been asked many times, and the method of importing this type of data is in the archives.

Please check the archives.