XML mapping issues - how to get the structure of the target in the mapping output?

[Jan_van_Ansem]

Hi Data Services guru's,

I am creating a dataflow which reads an xml file, transforms it, and subsequently writes it as a new XML file.

The source and target are different (the target having more fields, which should be populated by default values or fixed values).

Both source and targets have 1000s of fields and 100s of nested structures. (They originate from SAP CRM).

My problem is that I can't find a way to get the new fields / nested structures in the output schema of my transformation.

I can manually add them, but that is a lot of work.

When I try to copy and paste fields (from the target object to the transformation, they are added, but not necessarily in the right order.

How can I easily get the structure of my target xml format in the target section of my transformation?

Thanks for your help in advance.

Jan. 

[Former Member · Accepted solution]

Hi Jan,

First connect your query transform to the target XML without opening it, once connected open the query transform, the schema out will have all the columns and nested structures in it.

Make the nested schema as 'Make Current' in order to perform the mappings.

Hope this helps you.

Regards,

Sandeep

[Jan_van_Ansem · Accepted solution]

Thanks Sandeep, your suggestion works to get the required output schema.

I just realised I had missed one essential point in my question:

When I only connect an input to a transformation, not an output, and I open up the transformation, then I can simply drag and drop the fields from input to output and these fields are then automatically mapped.

When I connect an input and output to a transformation and then open up the transformation (as per your suggestion) I can see the output schema, but no mapping.

As I have several 100's nested structures and 1000's fields manual mapping would be time consuming.

About 90% of the structures in input schema and output schema is the same, with 1:1 mappings. I am looking for a way where I only have to do the 10% which is different.

Any help would be greatly appreciated.

Jan.

[Former Member · Accepted solution]

Hi Jan,

If the Names of columns of Input and Output schema are same, you can simply right click on the input schema column and click on map to output. It will get mapped to the column with same name.

Regards,
Sandeep

[Jan_van_Ansem · Accepted solution]

Hi Sandeep, when I try this, the schema is actually appended as a new schema underneath the existing schemas.

Here is a screenshot:

As you can see, it has also created a new nested schema with the same name and suffix _1.

Note that the source and target nested structures are not exactly the same, but they are 90% the same.

Perhaps the reason is because in some cases (< 5 %) there are more fields in the input than in the output? (In most cases, the differences is that the output schema has more fields than the input schema, which initially could just be left blank).

But even when I drill down to a sub-schema which is the same in Input and Output schema, Data Services refuses to map the corresponding fields automatically. It insist on appending a new structure instead.

[Former Member · Accepted solution]

Hi Jan,

Yes if you select the schema and map a new output schema would get generated.

What you have to do over here is expand the output schema which has to be mapped, then right click any of the column under it and select make current. Then select the columns from the input schema and map to output.

You can only map the output schema under nested scenario when the schema is made current, else it won't allow you to map.

[Jan_van_Ansem · Accepted solution]

Thanks Sandeep.

It is unfortunate, as I have 100s of nested schemas and 1000s of fields (the XML started its life as an IDOC for CRM Orders on SAP CRM).

Anyway, this being the case I know I just have to put in the effort to manually adjust it, and I am not missing a trick to automate this process.

Thanks for your input and points rewarded

Jan.

[former_member187605]

Why don't you use the XML_Map transform? It's been designed to that purpose. From the SAP Data Services Referenence Guide, section 5.6.9 XML_Map:

"

The XML_Map transform is a data transform engine designed for hierarchical data. It provides functionality similar to a typical XQuery or XSLT engine. The XML_Map transform takes one or more source data sets and produces a single target data set. Flat data structures such as database tables or flat files are also supported as both source and target data sets. You can use the XML_Map transform to perform a variety of tasks. For example:

● You can create a hierarchical target data structure such as XML or IDoc from a hierarchical source data structure.

● You can create a hierarchical target data structure based on data from flat tables.

● You can create a flat target data set such as a database table from data in a hierarchical source data structure.

"

[Jan_van_Ansem]

Thanks Dirk,

I had looked at the XML_MAP transform. But as both my source and my target are hierarchical I failed to see what XML_MAP has to offer above and beyond a normal query transformation.

I am working on a SAP CRM migration, ultimately using idocs as source and as target. I am writing xml purely for the validation processes, so I don't have to write into SAP CRM each time I do a test run.

Now unfortunately the source and target IDOCs are different, and very complex.

There are some segments in the source IDOC which are not in the target IDOC and other segments in the target IDOC which are not in the source.

I would consider using the XML_MAP if it would be easier to do the mapping or if it would perform better than standard query transform, but so far I have not seen evidence for this so I decided to stick to what I am familiar with.

What do you reckon, would it perform better or easier to get the mapping right? If so, I'll give it a try!

[former_member187605]

Maye you're right and there's not so much difference as far as the mapping exercise is concerned between both Query and XML_MAP transforms. I always use the latter, but that might be just a matter of habit. But note that you can select complete lists of attributes and groups and drag and drop them from source to target. There are also different ways to navigate thru the hierarches, you don't have to right-click all the time. All's explained in the Reference Guide.

XML_MAP comes with a major advantage when you're dealing with large inputs: the batch mode of the XML_MAP transform will help getting data thru. See Loading nested data in batches via XML_Map - Enterprise Information Management - SCN Wiki  for some clearer explanation.