Skip to Content
avatar image
Former Member

Map source XML structure into a FieldName+value target structure

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!!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 21, 2017 at 07:37 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 21, 2017 at 06:28 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 21, 2017 at 04:58 PM

    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!!

    Add comment
    10|10000 characters needed characters exceeded