none
Convert name value pair to flat structure in Biztalk mapper RRS feed

  • Question

  • Hi,

    I have an input xml file with name value pairs and have to access all the values in the same iteration to concatenate fname and lname and assign that to a fullname in the target schema.

    I am trying with logical eq functoid and value mapping flattening but i can't access both the fname and lname at the same time.

    Does any one have an idea on how to get this done?

    My input file is

    <?xml version="1.0" encoding="UTF-8"?>
    <InputData dateFormat="yyyyMMdd">
    <CharacteristicList>
        <characteristic>
          <name>fname</name>
          <value>hello</value>
        </characteristic>

        <characteristic>
          <name>lname</name>
          <value>world</value>
        </characteristic>

    </CharacteristicList>
    </InputData>


    And the output file is 

    <ns0:Root xmlns:ns0="http://test.Transforms.Schema1">
      <Test >
        <Fullname>Hello World</Fullname>
      </Test>
    </ns0:Root>

    Thanks


    Janardhan Reddy Bikka


    Monday, December 29, 2014 11:46 PM

Answers

  • Hi Janardhan,

    All you need is Cumulative Concatenate functiod with logical & value mapping functiod as shown:


    1. Add a Logical-Equal functiod. Link name element as its first parameter. And add “fname” as second parameter.
    2. Add another Logical-Equal functiod. Link name element as its first parameter. And add “lname” as second parameter.
    3. Add a Logical-Or functiod. Link the Logical-Equal functiod from fname as its first parameter. And Link the Logical-Equal functiod from lname as its second parameter
    4. Add value mapping (not the flattening one) functiod and link Logical-Or functiod.
    5. Add Cumulative Concatenate functiod. Link value mapping functiod to Cumulative Concatenate functiod as its first parameter. And add “1” as the second parameter. This second parameter provides the scope.
    6. Connect Cumulative Concatenate functiod to the fullname parameter.
    7. Add a looping functiod and connect parent CharacteristicList in source and Test in destination.

    With this map, if I use the input instance(XML) file as you have shown, I would get output as below:

    <ns0:Root xmlns:ns0="http://test.Transforms.Schema1">
      <Test>
        <Fullname>helloworld</Fullname>
      </Test>
    </ns0:Root>

    if I use following input with more than one Characteristlist

    <?xml version="1.0" encoding="UTF-8"?>
    <InputData dateFormat="yyyyMMdd">
    	<CharacteristicList>
    		<characteristic>
    			<name>fname</name>
    			<value>hello</value>
    		</characteristic>
    
    		<characteristic>
    			<name>lname</name>
    			<value>world</value>
    		</characteristic>
    
    	</CharacteristicList>
    	<CharacteristicList>
    		<characteristic>
    			<name>fname</name>
    			<value>Ashwin</value>
    		</characteristic>
    
    		<characteristic>
    			<name>lname</name>
    			<value>Prabhu</value>
    		</characteristic>
    
    	</CharacteristicList>
    </InputData>

    I will get output as below:

    <ns0:Root xmlns:ns0="http://test.Transforms.Schema1">
      <Test>
        <Fullname>helloworld</Fullname>
      </Test>
      <Test>
        <Fullname>AshwinPrabhu</Fullname>
      </Test>
    </ns0:Root>

    FYI: I have shown another sample instance with more first name and lastname because, you'r requirement is to have names concatenated with in a CharacteristicList




    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.


    Tuesday, December 30, 2014 4:10 PM

All replies

  • Question: Is there only 1 pair per characteristicList?

    If so you have easy solution, put for each loop functoid on Characterlist and then use combination of value mapping functoid and logical functoids like equals etc.

    For example, check value of name node, if equals = 'fname' then return corresponding value (string1)

    similarly check if value of name node = 'lname' then return value (string2) after than using concat functoid to join string1 and string 2

    If there are multiple name value pairs per CharacteristicList, using XSLT will make life easier.

    Tuesday, December 30, 2014 1:19 AM
  • Hi,

    Thanks for the reply!

    <CharacteristicList> is the top node and <characteristic> is the repeating which has single name/value pair.

    I guess looping functoid is used to combine multiple input fields to create a single field on the destination side. But in this case  what is the input and output to looping functoid?

    After getting the fname and lname i have to pass these 2 values to a string concatenate and nothing on the destination side directly.

    I tried different options with table looping, looping, custom xslt but could not get it done. I understand the tricky thing here is when we use the value mapping functoid BT creates a xs:foreach loop for each name value pair to get the value. But here when we are in the first iteration to get the fname, we have to loop through second to get the lname which is not working.

    If i dont understand correctly, can you please mention the steps?

    Cheers


    JB

    Tuesday, December 30, 2014 1:35 AM
  • Hi Janardhan,

    All you need is Cumulative Concatenate functiod with logical & value mapping functiod as shown:


    1. Add a Logical-Equal functiod. Link name element as its first parameter. And add “fname” as second parameter.
    2. Add another Logical-Equal functiod. Link name element as its first parameter. And add “lname” as second parameter.
    3. Add a Logical-Or functiod. Link the Logical-Equal functiod from fname as its first parameter. And Link the Logical-Equal functiod from lname as its second parameter
    4. Add value mapping (not the flattening one) functiod and link Logical-Or functiod.
    5. Add Cumulative Concatenate functiod. Link value mapping functiod to Cumulative Concatenate functiod as its first parameter. And add “1” as the second parameter. This second parameter provides the scope.
    6. Connect Cumulative Concatenate functiod to the fullname parameter.
    7. Add a looping functiod and connect parent CharacteristicList in source and Test in destination.

    With this map, if I use the input instance(XML) file as you have shown, I would get output as below:

    <ns0:Root xmlns:ns0="http://test.Transforms.Schema1">
      <Test>
        <Fullname>helloworld</Fullname>
      </Test>
    </ns0:Root>

    if I use following input with more than one Characteristlist

    <?xml version="1.0" encoding="UTF-8"?>
    <InputData dateFormat="yyyyMMdd">
    	<CharacteristicList>
    		<characteristic>
    			<name>fname</name>
    			<value>hello</value>
    		</characteristic>
    
    		<characteristic>
    			<name>lname</name>
    			<value>world</value>
    		</characteristic>
    
    	</CharacteristicList>
    	<CharacteristicList>
    		<characteristic>
    			<name>fname</name>
    			<value>Ashwin</value>
    		</characteristic>
    
    		<characteristic>
    			<name>lname</name>
    			<value>Prabhu</value>
    		</characteristic>
    
    	</CharacteristicList>
    </InputData>

    I will get output as below:

    <ns0:Root xmlns:ns0="http://test.Transforms.Schema1">
      <Test>
        <Fullname>helloworld</Fullname>
      </Test>
      <Test>
        <Fullname>AshwinPrabhu</Fullname>
      </Test>
    </ns0:Root>

    FYI: I have shown another sample instance with more first name and lastname because, you'r requirement is to have names concatenated with in a CharacteristicList




    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.


    Tuesday, December 30, 2014 4:10 PM
  • Hi Ashwin,

    Thank you for the solution and in fact my problem is little bit more complex than i explained here. It is just a test target schema that i created to work on POC but with my actual target schema i have the below constraints.

    1. get the fname and lname in the same iteration and pass it to the DB lookp functoid as individual values to retrive other values for this person.

    2. If len(fname) > 25 then fullname = fname else fullname = concate(fname+lname) 

    my intention is to get the fname and lname as individual values in the same iteration so that i can pass to either dblookup functoid or string concatenate or put some conditions on it. Does it make sense?

    we have only one <CharacteristicList> node as top node in the xml with multiple <Characteristic> nodes.

    Can you please advise how can we get this done?

    Thanks


    JB



    Tuesday, December 30, 2014 8:12 PM
  • Can you please paste target schema as well? and mark the node which needs fullname. There might be some easy alternative we can think of.

    Thanks

    Tuesday, December 30, 2014 8:36 PM
  • Hi,

    Thanks Ashwin for the solution.

    With little tweaking from Ashwin's solution, i just got the desired values from the map. Here is the list of steps that i tried.

    1. Add a Logical-Equal functiod. Link name element as its first parameter. And add “fname” as second parameter.
    2. Add another Logical-Equal functiod. Link name element as its first parameter. And add “lname” as second parameter.
    3. Add value mapping (not the flattening one) functiod and link fname Logicalfunctiod and link the output of this value mapping functoid to a Cumulative Concatenate functiod – This will give the fname value
    4. Add value mapping (not the flattening one) functiod and link lname Logicalfunctiod and link the output of this value mapping functoid to a Cumulative Concatenate functiod -  This will give the lname value
    5. Connect both the Cumulative Concatenate functoids to a string concatenate functoid if we want to concatenate with a “|” symbol in between.
    6. In my case I passed the output of both Cumulative Concatenate functoids to a custom database functoid to execute a stored procedure.

    


    JB

    Tuesday, December 30, 2014 9:09 PM