Writes data to a file from a SQL statement.

Syntax

xp_write_file( 
 filename,
 file_contents
)

Arguments

Remarks

The function writes file_contents to the file filename. It returns 0 if successful, and non-zero if it fails.

The filename value can be prefixed by either an absolute or a relative path. If filename is prefixed by a relative path, then the file name is relative to the current working directory of the database server. If the file already exists, its contents are overwritten.

This function can be useful for unloading long binary data into files.

You can also use the CSCONVERT function to address character set conversion requirements you have when using the xp_write_file system procedure. See CSCONVERT function [String].

Permissions

DBA authority required

See also

• CSCONVERT function [String]
• xp_read_file system procedure
• CALL statement

Examples

This example uses xp_write_file to create a file accountnum.txt containing the data 123456:

CALL xp_write_file( 'accountnum.txt', '123456' );

This example queries the Contacts table of the sample database, and then creates a text file for each contact living in New Jersey. Each text file is named using a concatenation of the contact's first name (GivenName), last name (Surname), and then the string .txt (for example, Reeves_Scott.txt), and contains the contact's street address (Street), city (City), and state (State), on separate lines.

SELECT xp_write_file( 
Surname || '_' ||  GivenName || '.txt', 
Street || '\n' || City || '\n' || State ) 
FROM Contacts WHERE State = 'NJ';

This example uses xp_write_file to create an image file (JPG) for every product in the Products table. Each value of the ID column becomes a file name for a file with the contents of the corresponding value of the Photo column:

SELECT xp_write_file( ID || '.jpg' , Photo ) FROM Products;

In the example above, ID is a row with a UNIQUE constraint. This is important to ensure that a file isn't overwritten with the contents of subsequent row. Also, you must specify the file extension applicable to the data stored in the column. In this case, the Products.Photo stores image data (JPGs).