Skip to Content
0

Map source XML structure into a FieldName+value target structure

Jun 20, 2017 at 09:03 PM

76

avatar image

Hello experts - what's the best option to map a source single-node XML to a target generic 2-field structure?

Notice the example below. I would need to map the field name "ProtocolNumber" to the 'field' element in the target structure, and map it's value, "123", to the field/val element.

The target field element would repeat for each source record.

Source

   <Record>
      <ProtocolNumber>123</ProtocolNumber>
      <QuoteNumber>9999</QuoteNumber>
      <QuoteItem>1</QuoteItem>
      <QuoteStatus>Approved</QuoteStatus>
   </Record>

Target

<_0:ParamValues>
	<_0:field column="ProtocolNumber">
		<_0:val>123</_0:val>
	</_0:field>
	<_0:field column="QuoteNumber">
		<_0:val>9999</_0:val>
	</_0:field>
	<_0:field column="QuoteItem">
		<_0:val>1</_0:val>
	</_0:field>
	<_0:field column="QuoteStatus">
		<_0:val>Approved</_0:val>
	</_0:field>
</_0:ParamValues>

Thank you!!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Evgeniy Kolmakov Jun 21, 2017 at 07:37 PM
3

Hi Elison!

I would do it using XSLT transformation, since it could be the generic template not hard-linked to source structure. Even if your Record element's inner structure is changed - template would work as desired.

Source:

<?xml version="1.0" encoding="UTF-8"?>
<MT_Source>
    <Record>
        <ProtocolNumber>123</ProtocolNumber>
        <QuoteNumber>9999</QuoteNumber>
        <QuoteItem>1</QuoteItem>
        <QuoteStatus>Approved</QuoteStatus>
    </Record>
    <Record>
        <ProtocolNumber>456</ProtocolNumber>
        <QuoteNumber>8888</QuoteNumber>
        <QuoteItem>2</QuoteItem>
        <QuoteStatus>Not Approved</QuoteStatus>
    </Record>
</MT_Source>

Transformation:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:_0="http://tempuri.org" version="1.0">
    <xsl:template match="/">
        <_MT_Out>
            <xsl:apply-templates select="//Record"/>
        </_MT_Out>
    </xsl:template>
    <xsl:template match="Record">
        <_0:ParamValues>
            <xsl:for-each select="./*">
                <_0:field column="{local-name()}">
                    <_0:val> <xsl:value-of select="."/> </_0:val>
                </_0:field>    
            </xsl:for-each>
        </_0:ParamValues>    
    </xsl:template>
</xsl:stylesheet>

Target:

<?xml version="1.0" encoding="utf-8"?>
<_MT_Out xmlns:_0="http://tempuri.org">
    <_0:ParamValues>
        <_0:field column="ProtocolNumber">
            <_0:val>123</_0:val>
        </_0:field>
        <_0:field column="QuoteNumber">
            <_0:val>9999</_0:val>
        </_0:field>
        <_0:field column="QuoteItem">
            <_0:val>1</_0:val>
        </_0:field>
        <_0:field column="QuoteStatus">
            <_0:val>Approved</_0:val>
        </_0:field>
    </_0:ParamValues>
    <_0:ParamValues>
        <_0:field column="ProtocolNumber">
            <_0:val>456</_0:val>
        </_0:field>
        <_0:field column="QuoteNumber">
            <_0:val>8888</_0:val>
        </_0:field>
        <_0:field column="QuoteItem">
            <_0:val>2</_0:val>
        </_0:field>
        <_0:field column="QuoteStatus">
            <_0:val>Not Approved</_0:val>
        </_0:field>
    </_0:ParamValues>
</_MT_Out>

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded
Anoop Rai Jun 21, 2017 at 06:28 AM
0

Hi,

Use "Attribute" for Parent Element and assign a constant with Source Field name to it. Then add a child node, with val name and assign Source Value to it.

Regards,

Anoop Kumar Rai


capture.jpg (73.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
Elison Avila Chagas Lima Jun 21, 2017 at 04:58 PM
0

Hello Anoop - I'm ok with the field/value assignment. I'm more interested in finding a good way to have the target structure field replicated to each element within the 'Record' source structure.

I'm skeptical that could be achieved over the graphic mapping, so can it be done through a UDF? Does it need Java or XSLT? Is there a code example?

Thank you!!

Share
10 |10000 characters needed characters left characters exceeded