Unnesting XML Schema in Data Services

dataservices data quality
1

Hi,

I have an XML output which is nested. I have already unnested the schema out and mapped it to a table(relational:Oracle 10g).However, I got multiple rows in my output. I need just a single row with all the not null data elements in it. Is it possible?

Thanks in advance,

abhi007 (BOB member since 2009-07-16)

2

https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Separating+a+NRDM+node

should be what you are looking for.

Or this:
https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Unnest+or+NRDM+join

Werner Daehn :de: (BOB member since 2004-12-17)

3

Hi,

Thanks for the response. I did see the links but was not able to relate to my problem in totality.I have attached a screenshot of my query which is within the dataflow I created.Please have a look at the schema out which I have completely unnested. I have taken the output of the same and connected to my table.This is where the problem lies.

let me know if someone can help :expressionless:
schema_diagram.zip (40.0 KB)

abhi007 (BOB member since 2009-07-16)

4

You have the Query with two schemas at the same level

Query
±-SINONMSGSRSV1
–±-…
±-CREDITMSGSRSV2
–±-…

Both schemas represent an array of data, a table if you will. By unnesting both at the same time you are creating a cartesian product of these two tables. It is like joining two relational tables that have no join condition.

I assume you want to load the XML data into multiple output tables, so you need multiple queries where take the input schema and unnest the part you care about for this query as shown in the Separating+a+NRDM+node

Werner Daehn :de: (BOB member since 2004-12-17)

5

Thanks Werner. I have actually put all my unnested data in a single table and have written an sql query to pull out all the details based on the key.

So it works for me now!

Regards,
Abhishek

abhi007 (BOB member since 2009-07-16)

6

Hi Werner

I am having the same issue whereby NULL rows are being created in oracle. I have taken the approach you have suggested which is to use “multiple queries which take the input schema and unnest the part you care about for this query as shown in the Separating+a+NRDM+node”.

I still get the NULL rows. I can handle it the same way as abhi as done, which is write to a table and select rows where columns are not null, however I would prefer not to.

I have attached the atl, xsd and sample xml.
ian.zip (15.0 KB)

Ian Ross (BOB member since 2011-11-08)

7

I am at the SAP TechEd at the moment, so will not be able to look at the ATL. But it has to be related to from/where clause of the different levels. In DS there is no “NULL row”. If you find a row with all values being NULL, that means your from/where clause of this schema produced one row. And the mappings assigned NULL values to each column.

I can’t think of any other explanation - maybe the schema is represented by XML Attributes and not XML Elements, that might change the behavior slightly, but I doubt it is the cause either.

Werner Daehn :de: (BOB member since 2004-12-17)

8

Hi Werner

I did think of this earlier but the “from” and “where” appear to be fine. Interestingly I found that when i extract the tag there are null rows created only if the tag is present in the xml file being loaded, otherwise no null rows are created. Cant see any reason why this would happen.

Would it be possible for you to take a look at the atl when you get a chance?

Thanks
Ian.

Ian Ross (BOB member since 2011-11-08)