XML Output stage not aggregating XML correctly

[dav_mcnair]

I am outputting some store level records from an ODBC stage via a query and the XML Output stage is not outputting my first record correctly. This only happens on the first records the other 1000 output fine. Any ideas?

Dataset:
RESTID RESTDESC RESTMAILADDR1 RESTMAILADDR2 RESTMAILCITYNM RESTMAILSTATEID RESTMAILZIP
004500 STORE
004507 STORE

Output XML:
<STORE>
<STOREID>004500</STOREID>
<STOREID>004507</STOREID>
<DESC>STORE</DESC>
<ADDR1 />
<ADDR2 />
<CITY />
<STATE />
<ZIP />
</STORE>

Expected Output:
<STORE>
<STOREID>004500</STOREID>
<DESC>STORE</DESC>
<ADDR1 />
<ADDR2 />
<CITY />
<STATE />
<ZIP />
</STORE>
<STORE>
<STOREID>004500</STOREID>
<DESC>STORE</DESC>
<ADDR1 />
<ADDR2 />
<CITY />
<STATE />
<ZIP />
</STORE>

[JoshGeorge]

You have not identified and defined the repetition element properly.

Any ideas?

[dav_mcnair]

I do not see a place on the XML Output stage to specify the repition element. I do see this option on the XMLInput stage. I have a key defined in the column input. I am on 7.5.2.

[chulett]

You do it in the exact same way - mark the field as a Key on the Columns tab.

[dav_mcnair]

Unfortunately it did not work... See below for column layout... Any more ideas??? I even switched to gridview mode of the XMLOutput stage and changed the "Trigger Column" field to RESTID. I could not locate the Trigger Column field in the regular view.

Column name Key SQL type Extended Length Scale Nullable Display Data element Description

RESTID Yes VarChar 10 No 11 /STORES /STORE /STOREID

RESTDESC No VarChar 30 No 30 /STORES /STORE /DESC

RESTMAILADDR No VarChar 35 No 35 /STORES /STORE /ADDR 1

RESTMAILADDR No VarChar 35 No 35 /STORES /STORE /ADDR 2

RESTMAILCITYNM No VarChar 15 No 15 /STORES /STORE /CITY


RESTMAILSTATEID No VarChar 2 No 2 /STORES /STORE /STATE

RESTMAILZIP No VarChar 10 No 10 /STORES /STORE /ZIP

[dav_mcnair]

Unfortunately it did not work... See below for column layout... Any more ideas??? I even switched to gridview mode of the XMLOutput stage and changed the "Trigger Column" field to RESTID. I could not locate the Trigger Column field in the regular view.

Column name Key SQL type Extended Length Scale Nullable Display Data element Description

RESTID Yes VarChar 10 No 11 /STORES /STORE /STOREID

RESTDESC No VarChar 30 No 30 /STORES /STORE /DESC

RESTMAILADDR No VarChar 35 No 35 /STORES /STORE /ADDR 1

RESTMAILADDR No VarChar 35 No 35 /STORES /STORE /ADDR 2

RESTMAILCITYNM No VarChar 15 No 15 /STORES /STORE /CITY


RESTMAILSTATEID No VarChar 2 No 2 /STORES /STORE /STATE

RESTMAILZIP No VarChar 10 No 10 /STORES /STORE /ZIP

[JoshGeorge]

In the input column tab of your XML output stage specify key as any non-nullable column other than your first column ie. STOREID. For example try with STATE as key. Also make sure in the Output -> Transformation Settings tab you have checked Output Mode as 'Aggregate all rows'.

[chulett]

The 'repetition element' is generally the lowest level element, not the highest. And the Trigger Column controls when a new file is created, triggered whenever the value of that column changes.

[JoshGeorge]

In the above case everything after "/STORES /STORE /" falls into the same level and any field in that level is to be considered as 'repetition element'.

"/STORES /STORE /<repetition element level (lowest)>"

To get different store information as repetitive level (blocks) of aggregated rows in the same XML, if "/STORES /STORE /<STOREID>" is defined as key the result is as OP noted in the original post. In the same level specify key as any non-nullable column other than this first element (ie. STOREID), expected output is the result.