Convert row to columns in BLS

[Former Member]

Hi All,

There is data in grid format

example

a b c d
e f g h
i j k l

This has to be sent to RFC in the same format as table

I took one row as comma separated in BLS converted string list to XML but it appears under one column

item 
|_a
|_b
|_c
|_d

a,b,c,d appears as row but I need to map it as columns in Illuminator document

That is a,b,c,d which appears as row needs to be converted to columns so that they can be mapped to illuminator document

Thanks in advance

Regards

Namita

[Former Member]

If your source is SQL 2005, you can do this in the query using the UNPIVOT command, but if you have to do it in BLS, its not TOO terrible, you just need to nest a repeater.

You'll need 3 repeaters, the first one is a row repeater, you will use this to define your columns. Basically take the output of the repeater, then use the xmlcolumn action to define the names of your output column. If you want the first column from your original source document to be the names of the columns in your output, then use bind the "name" property of the xmlcolumn action to the First column of the source document. It will look something like this

Repeater_0.Output{/Row/Col1} 

Where "Col1" is the Column you want to get your names.

Now that you have defined your column names, you can end that repeater. The next repeater will be a row repeater with source XML being /Rowsets/Rowset/Row, nested in that you put another repeater using the columns /Rowsets/Rowset/Columns/Column. Now here's the tricky part, the first time you run through the column repeater, you need to create rows, but after that, the rows are created so you just need to assign a value to the XML, fortunatly, thats easy enough to do with a conditional (CurrentItem == 1) So the first time through the repeater, you do an XML Row, since you created your column headers dynamically, you will have to assign the "value" property something like

 xmldocOutput.Output{/Rowsets/Rowset/Row[#repRow.CurrentItem#]/#repCol.Output{/Column/@Name}#} 

After the first time through the repeater, you just use the xml Data Item, you can do the assignment in the same way

 xmldocOutput.Output{/Rowsets/Rowset/Row[#repRow.CurrentItem#]/#repCol.Output{/Column/@Name}#} 

This is how I've done it in BLS. You can also repeat through row, build a local string, use StringList To XML Parser block to do something like this. But personally I like the nested repeater method.

Sorry this isn't easier, its a lot easier to see with the actual transaction, but this is how I do it. Maybe somebody else has a better method.

[Former Member]

Created columns dynamically

[agentry_src]

Hi Namita,

Use StringToXML, not StringListToXML. StringList assumes a single column output. You will have to play around with the xml format a bit to make it convert correctly. And will also need to use a repeater (or some clever xPath).

Good luck,

Mike

Edited by: Michael Appleby on Sep 11, 2008 4:23 PM