cancel
Showing results for 
Search instead for 
Did you mean: 

how to load xml file to IQ table

Former Member
0 Kudos

Hi,

I created a table called stg.testxml


create table stg.testxml

(

XMLData text;

)

and then i tried the following command.


load table stg.testxml(

XMLData

)

Using Client File 'd:\\test_20160422.xml'

ESCAPES OFF;

commit;

it loads the data to the table but i got 800+ rows. however, I wanted to load as one row instead of multiple rows.

the XML has row delimiter as {LF}.  is there anyway I can load it as one record??

thanks

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member194571
Active Participant
0 Kudos

Hi Jay,

I got one step further, and that's probably how far I can get with the environment I have at hand (without UDA).

The goal is to have a table populated with the name and content of files I've specified in the file I load from.

>>

create table CLOBs (filename varchar (320) not null iq unique (0), content varchar (32750) not null iq unique (0));

load table CLOBs (filename (','), content ascii file (','))
using 'E:\Projekte\FlightStats\IQ\IQ16Win\CLOB_wrapper.dat'
escapes off quotes off;

-- CLOB_wrapper.dat

E:\Projekte\FlightStats\IQ\IQ16Win\3aTools.SQL,E:\Projekte\FlightStats\IQ\IQ16Win\3aTools.SQL,

E:\Projekte\FlightStats\IQ\IQ16Win\3aToolsParam.SQL,E:\Projekte\FlightStats\IQ\IQ16Win\3aToolsParam.SQL,

E:\Projekte\FlightStats\IQ\IQ16Win\4bLoadFileLean.SQL,E:\Projekte\FlightStats\IQ\IQ16Win\4bLoadFileLean.SQL,

E:\Projekte\FlightStats\IQ\IQ16Win\3Tables.SQL,E:\Projekte\FlightStats\IQ\IQ16Win\3Tables.SQL,

E:\Projekte\FlightStats\IQ\IQ16Win\5Eval.SQL,E:\Projekte\FlightStats\IQ\IQ16Win\5Eval.SQL,

<<

The load operation fails weith the error message that the ASCII FILE clause in the LOAD statement is only valid for long varchar (or "text") columns, which in turn require the UDA license. Also, I'd like to repeat that this is all about server side files.

Maybe this helps you to get closer to what you try to achieve...

HTH

Volker

Former Member
0 Kudos

i don't think it's possible to load entire file content to a row in IQ since I don't have unique row delimiter or column delimiter.  I believe only way to hack this is to add an unique value to the end of XML and use that as row delimiter.

former_member194571
Active Participant
0 Kudos

The point is, you don't need a delimiter in the .xml file, you need one in the file where you specify the file name as the source of your XML value.

Unfortunately, I don't have an IQ installation at hand to verify, but it should look somehow like this (for server files):

-- xmlwrapper.dat

'd:\test_20160422.xml',

-- LOAD statement

load table stg.testxml(XMLData ASCII File (',')) 

using file 'xmlwrapper.dat' escapes off quotes on;

Imagine we'd store terabytes of CLOB and / or BLOB containers in an IQ database, how at all could we load them if all LOB values would have to be stored literally in the load file. This 1-LOB - 1-file. approach IMO is the most reasonable way for non-trivial amounts of data.

Do you have an Unstructured Data license? It might be a requirement for this method.

Also, as stated above, I don't know if it works for client files. My suggestion is to try with server side files and add the client file load as an extra bit of complexity if the easier setup worked.

HTH

Volker

former_member194571
Active Participant
0 Kudos

I think the file name should be unquoted so you don't need the quotes on clause.

former_member194571
Active Participant
0 Kudos

Wouldn't you use the extended LOAD syntax [Extended LOAD TABLE Syntax] in such a case,

creating a wrapper load file which contains the XML file name using the ASCII FILE (...) reference? So to indicate that you want to load the file content as a CLOB value rather than load data rows from a file?

I don't know if this works for client files, however.

HTH

Volker

hans-juergen_schwindke
Active Participant
0 Kudos

Hi,

have you played with the options of load table:

DELIMITED BY ','

ROW DELIMITED BY '\n'

Delimited refers to columns, so you probably need "row delimited".

I've never loaded a xml file into IQ but I would try these options. Maybe you have to add a unique delmiter at the very end of the file.

Best regards,

Juergen

markmumy
Advisor
Advisor
0 Kudos

That's correct.  You will need to specify the row and column delimiters when loading data.  There are some caveats to this with defaults, but its best practice to always specify.

With an XML file, you need to make sure that your row delimiter does not exist in the data.  Carriage returns or line feeds exist all over XML data.  Best to use a non-printable character or multiple characters (up to 4) for your delimiters.

Second, the XML data can only be loaded into a CLOB/BLOB column (or varchar/varbinary if under 32k).  Not an issue as you are using TEXT (CLOB).

Mark