The Database communication point in Input mode and Database Message Extraction filter produces an outgoing message in XML. The following properties can be used to specify how to display the result in XML message format:

Property

Description

ColumnNameAsAttribute

If this property exists then the output messages will have the column name as an attribute instead of as an element name.

RowNameAsAttribute

If this property exists then the output messages will have the row name as an attribute instead of as an element name.

ValueAsAttribute

If this property exists then the output messages will have the column result value as an attribute instead of as a CDATA section.

rowsInMessage

The number of rows to be used to generate an outgoing message out of the root statement result set. Typically, <rowsInMessage>1</rowsInMessage> is used to generate an XML message per each row. In this case, the transaction of the root SELECT SQL statement is executed when all the rows previously returned are processed.

This property is only available for the Database Message Extraction filter if its Message Body Column configuration property is specified.

The properties are not applicable to the Database communication point in Output mode and the Database Lookup filter even though they may appear as elements in the XML output.

Syntax

<ColumnNameAsAttribute/>
<RowNameAsAttribute/>
<ValueAsAttribute/>

The general form of the XML message is as follows. The order of the XML elements is the same as the order of the columns in the database results set.

<message>
    <rowname1>
        <columnName1>column value</columnName1>
        <columnName2>column value</columnName2>
        <columnName3>column value</columnName3>
        ...
    </rowname1>
    <rowname2>
        <columnName1>column value</columnName1>
        <columnName2>column value</columnName2>
        <columnName3>column value</columnName3>
        ...
    </rowname2>
    <rowname3>
        <columnName1>column value</columnName1>
        <columnName2>column value</columnName2>
        <columnName3>column value</columnName3>
        ... 
    </rowname3>
</message>

Essentially for each statement, starting at the root statement, you have:

<rowName>
    <columnName>column value</columnName>
    <columnName>column value</columnName>
    <columnName>column value</columnName>
</rowName>

groups of elements for each row returned by the SQL statement from the database. The row group contains exactly one column element for each column in the row returned and then contains row groups for every child statement.

The following listing is a sample XML configuration file for a Database communication point configured in Input mode:

<statement name="main">
    <sql>
        SELECT id, date FROM main_table WHERE date > ? ORDER BY date ASC
    </sql>
    <statement name="names">
        <sql>
            SELECT firstName, secondName FROM names_table WHERE id = @id
        </sql>
    </statement>
    <statement name="address">
        <sql>
            SELECT address, country_code FROM address_table WHERE id = @id
        </sql>
        <statement name="country">
            <sql>
                SELECT country_name FROM country_table WHERE country_code = @country_code
            </sql>
        </statement>
        </statement>
</statement>

Assume the database tables used in this example have the data from the following tables:

id

date

97320

2002-1-1 12:12:34

23409

2002-1-1 12:14:56

20234

2002-1-2 10:32:25

Data for main_table

id

firstName

secondName

97320

bob

brown

23409

doug

green

20234

mary

johns

Data for names_table

id

address

country_code

97320

12 Nowhere St

US

23409

43 Higher Ave

NZ

20234

123 Long Rd

UK

20234

321 Short St

US

Data for address_table

country_code

country_name

US

United States

NZ

New Zealand

UK

United Kingdom

Data for country_table

Then the XML produced would be:

<message>
    <main>
        <id>97320</id>
        <date>2002-1-1 12:12:34</date>
        <names>
            <firstName>bob</firstName>
            <secondName>brown</secondName>
        </names>
        <address>
            <address>12 Nowhere St</address>
            <country_code>US</country_code>
            <country>
                <country_name>United States</country_name>
            </country>
        </address>
    </main>
    <main>
        <id>23409</id>
        <date>2002-1-1 12:14:56</date>
        <names>
            <firstName>doug</firstName>
            <secondName>green</secondName>
        </names>
        <address>
            <address>43 Higher Ave</address>
            <country_code>NZ</country_code>
            <country>
                <country_name>New Zealand</country_name>
            </country>
        </address>
    </main>
    <main>
        <id>20234</id>
        <date>2002-1-2 10:32:25</date>
        <names>
            <firstName>mary</firstName>
            <secondName>johns</secondName>
        </names>
        <address>
            <address>123 Long Rd</address>
            <country_code>UK<country_code>
            <country>
                <country_name>United Kingdom</country_name>
            </country>
        </address>
        <address>
            <address>321 Short St</address>
            <country_code>US</country_code>
            <country>
                <country_name>United States</country_name>
            </country>
        </address>
    </main>
</message>

The output XML can be changed by using the columnNameAsAttribute, rowNameAsAttribute, valueAsAttribute configuration properties. If columnNameAsAttribute is specified, the columnName, instead of being used as the element name in the XML, is included as the value of a name attribute and the element is called column instead. Similarly the row name can be included as an attribute instead of as an element name. If valueAsAttribute is specified, the column result, instead of being the element CDATA in the XML, is included as the value of a value attribute.

So if both columnNameAsAttribute, rowNameAsAttribute, and valueAsAttribute exist in the configuration file, the above XML would become:

<message>
    <row name="main">
        <column name="id" value="97320"/>
        <column name="date" value="2002-1-1 12:12:34"/>
        <row name="names">
            <column name="firstName" value="bob"/>
            <column name="secondName" value="brown"/>
        </row>
        <row name="address">
            <column name="address" value="12 Nowhere St"/>
            <column name="country_code" value="US"/>
            <row name="country">
                <column name="country_name" value="United States"/>
            </row>
        </row>
    </row>
    <row name="main">
        <column name="id" value="23409"/>
        ...
</message>