Page 1 of 1
XML Output stage not aggregating XML correctly
Posted: Thu Nov 01, 2007 12:40 pm
by 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>
Re: XML Output stage not aggregating XML correctly
Posted: Thu Nov 01, 2007 5:28 pm
by JoshGeorge
You have not identified and defined the repetition element properly.
dav_mcnair wrote:
Any ideas?
Posted: Thu Nov 01, 2007 7:29 pm
by 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.
Posted: Fri Nov 02, 2007 6:52 am
by chulett
You do it in the exact same way - mark the field as a Key on the Columns tab.
Posted: Fri Nov 02, 2007 7:59 am
by 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
Posted: Fri Nov 02, 2007 8:00 am
by 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
Posted: Sun Nov 04, 2007 6:54 pm
by 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'.
Posted: Sun Nov 04, 2007 9:15 pm
by 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.
Posted: Mon Nov 05, 2007 1:59 am
by 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.