XML node content

[#1]

Hi

I have the following XML section:

<component>

     <section>

          <code code="S006" codeSystem="2.16.840.1.113883.4.292.30.14.1.2" displayName="Comentarios adicionales" />

          <title>COMENTARIO SOBRE EL ESTUDIO</title>

     </section>

</component>

and I am reading the attribute (code="S004") using LOAD:

LOAD

  code as Post_Score

FROM [lib://NEW/*.xml](XmlSimple, embedded labels, table is [component/section/code])

WHERE displayName='Comentarios adicionales';

I want to load the letter 'A' instead of "S004". (load 'A' whenever I read "S004)

How can I do that?

[Gysbert_Wassenaar · Accepted solution]

LOAD

     If(code='S004','A',code) as Post_Score

FROM

     ...

[#3]

Thanks a lot Gysbert.
What if I need to add several if statements for several possibilities?

For example: 

     If(code='S004','A',code) as Post_Score

     If(code='S003','B',code) as Post_Score

     ........

     ........

[Gysbert_Wassenaar]

You can nest if statements:

if(code='S004','A',if(code='S003','B',code)) as Post_Score

You can use a pick-match combination:

pick(1+match(code, 'S004','S003', ...etc... ), code, 'A', 'B', ...etc) as Post_Score

And if you have a lot of codes to replace you can use a mapping table and the applymap function:

mapCodes:

MAPPING LOAD * INLINE [

Old, New

S004, A

S003, B

...etc

];

TableA:

LOAD

     ApplyMap('mapCodes', code) as Post_Score,

     ...etc

FROM

     ...