How to insert XML data into DB2

When you start working with XML in the database, one of your first statements after creating a table with an XML column typically is to insert the "Hello World" test:

insert into foo values(1,'<test>Hello World</test>')

It's great to test out XML in the database and seeing is believing. However, later, when dealing with your real application, statements like the one above are not such a great idea for a couple reasons:

• Because you provide the inserted data as constant, it's harder for DB2 to optimize the statements. You should use parameter markers or host variables to provide the data if you want to use the insert statement. The benefit is that statements are prepared only once and it's also less statement parsing for the DB2 compiler. In other words, you save money and the planet...
• Another reason to avoid (string) constants is that their size is limited. Even though a SQL statement can be up to 2 GB in length (that's a lot of typing, but easy for statement generators), string constants are limited to 32 kb and you risk running into error SQL0102N.

If you are inserting bulk data, you should also consider import or load. For both of them, XML data can be kept in files.