XML complex import

Dec 03, 2013

Marcos Buccellato wrote

I have to parse a ONIX XML document where I have to pick a field conditionaly. for example I get this:

1
A01

Barry Faber

1
B06

Sergio Bulat Barreiro

Here I have to pick the contributor that has ContributorRole = B06 and place it in CSV field and discard the other.

I thought about concatennating all fields in one row and the use an expression to select the correct value

ie: A01|Barry Faber|B06|Sergio Bulat Barreiro

and then use a while adn substrin_index combination to compare the values and pick the wright one but I cannot seem to get the while statement working on the expression editor.

any clues?

5 Answers

Dec 03, 2013

Marcos Buccellato wrote

sorry XML tags where filtered... change the with *

*Contributor*
*SequenceNumber*1*/SequenceNumber*
*ContributorRole*A01*/ContributorRole*
*PersonName*Barry Faber*/PersonName*
*/Contributor*
*Contributor*
<SequenceNumber>1</SequenceNumber>
<ContributorRole>B06</ContributorRole>
<PersonName>Sergio Bulat Barreiro</PersonName>
</Contributor>

Dec 03, 2013

Marcos Buccellato wrote

I managed to extract a conditional value in a very unelegant way using IF statements and recursive SUBSTRING_INDEX. If someone has a better method it would bevery muche welcomed.

IE: In this example I want to take the price that has pricetype 03.

<Price>
<PriceType>01</PriceType>
<PriceAmount>32.42920000000000</PriceAmount>
<CurrencyCode>ARS</CurrencyCode>
</Price>
<Price>
<PriceType>03</PriceType>
<PriceAmount>32.42920000000000</PriceAmount>
<CurrencyCode>ARS</CurrencyCode>
</Price>
<Price>
<PriceType>41</PriceType>
<PriceAmount>32.42920000000000</PriceAmount>
<CurrencyCode>ARS</CurrencyCode>
</Price>

IF (
SUBSTRING_INDEX([CSV_COL(7)],'|',1)='03',
SUBSTRING_INDEX(SUBSTRING_INDEX([CSV_COL(7)],'|',2),'|',-1),
IF (
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX([CSV_COL(7)],'|',3),'|',-2),'|',-1)='03',
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX([CSV_COL(7)],'|',4),'|',-3),'|',-2),'|',-1),
iF(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX([CSV_COL(7)],'|',5),'|',-4),'|',-3),'|',-2),'|',-1)='03',
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX([CSV_COL(7)],'|',6),'|',-5),'|',-4),'|',-3),'|',-2),'|',-1),
0)
))

Dec 03, 2013

Marcos Buccellato wrote

Just to be clear, I concatenated all the XML data in one column with '|' as a separator.

Dec 04, 2013

Ira Kobylianska agent wrote

Thank you for your reply.

Please try the following expression:
IF([CSV_COL(1)] = '03', [CSV_COL(2)], '')

Let us know if you have more questions

2013-12-04_1945.png

Dec 04, 2013

Marcos Buccellato wrote

Excellent thanks!