When using SQL, the XML formatter converts rows to XML that you define in a foreign table or foreign stream, outputting these rows to a data writer that you have defined for the foreign table or stream. When you INSERT into the foreign table or stream, s-Server begins writing XML to the data writer that you specify.
When using the ECD agent, the XML converts rows that you define in a properties file to XML, outputting these rows to a data writer that you have defined for the foreign table or stream.
For a list of writers, and details on how to use them, see the Writing to Other Destinations.
For performance reasons, most formatting should be done in native SQL and passed into ECDA, not pushed into ECDA formatters.
To write XML data, you need to give the Extensible Common Data Adapter the following information:
To write XML data, you create a foreign table or stream that references one of s-Server's prebuilt server objects. Like all tables and streams, foreign tables and streams must be created within a schema. The example below creates and sets a schema called xml_data and creates a foreign stream called xml_writer_stream that writes data to the file system.
To write data over other input/output systems, such as Kafka, Kinesis, a network socket, a WebSocket, HTTP or AMQP, you would need to specify options for these formats. See Writing to Other Destinations for more details.
CREATE OR REPLACE SCHEMA xml_data; SET SCHEMA 'xml_data'; CREATE OR REPLACE FOREIGN STREAM xml_writer_stream ("STR1" VARCHAR(32)) SERVER FILE_SERVER OPTIONS (directory 'path/to/myfile', formatter 'XML', "CHARACTER_ENCODING" 'UTF-8', "DIRECTORY" '/home/guavus/output', "ORIGINAL_FILENAME" 'stocks-output.xml', "FILENAME_PREFIX" 'output-', "FILENAME_SUFFIX" '.xml', "FILENAME_DATE_FORMAT" 'yyyy-MM-dd-HH:mm:ss', "FILE_ROTATION_SIZE" '20K' "FORMATTER_INCLUDE_ROWTIME" 'true' );
To actually write to a file in path/to/myfile, you need to write a pump containing an INSERT statement along the following lines:
CREATE OR REPLACE PUMP "writerPump" STARTED AS INSERT INTO xml_writer_stream SELECT STREAM "MyStream"; --where "MyStream" is a currently existing stream
|FORMATTER||This needs to be XML.|
|DOC_ELEMENTS||Specifies a list of elements, separated by slashes ( /), to make as the root of the XML document to write. Defaults to "batch".|
|ROW_ELEMENTS||Specifies a list of elements, separated by slashes ( /), to add for each row of the XML document's DOM. Defaults to "row".|
|DATA_ELEMENTS||Specifies a list of elements, separated by slashes ( /), to add for each datum in a row/tuple. You must define DATA_ELEMENTS or DATA_ATTRIBUTES but not both. Using both will produce inconsistent XML.|
|DATA_ATTRIBUTES||Specifies a name of an attribute to add for each datum in a row/tuple. You must define DATA_ELEMENTS or DATA_ATTRIBUTES but not both. Using both will produce inconsistent XML.|
|Specifies a list of elements, separated by slashes ( /), to add for a specific datum in each row/tuple.|
|Specifies a name of an attribute to add for a specific column's datum in each row/tuple.|
|CHARACTER_ENCODING||Character set for data.|
|FORMATTER_INCLUDE_ROWTIME||Whether or not to include rowtime when writing data. Defaults to 'true'.|
|STARTING_OUTPUT_ROWTIME||If this is set, then all rows with ROWTIME less than the specified value are silently dropped. It is specified using an ISO 8601 format like '2020-02-06T12:34:56.789'.|
You can use the ECD agent to XML Data to remote locations. See Writing Data to Remote Locations for more details.
The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.
# Column types for the source stream ROWTYPE=RecordType(INTEGER COL1,TIMESTAMP COL2, INTEGER COL3, BOOLEAN COL4, VARCHAR(32) COL5, VARCHAR(32) COL6, INTEGER COL7) FORMATTER=XML CHARACTER_ENCODING=UTF-8 DIRECTORY=/home/guavus/output ORIGINAL_FILENAME=stocks-output.xml FILENAME_PREFIX=output- FILENAME_SUFFIX=.xml FILENAME_DATE_FORMAT=yyyy-MM-dd-HH:mm:ss FILE_ROTATION_SIZE=20K FORMATTER_INCLUDE_ROWTIME=true
To invoke the agent, from the directory $SQLSTREAM_HOME/../clienttools/EcdaAgent/ enter the following at the command line
$ ./commondataagent.sh --output --props sample.properties --io file