BusinessObjects Board

XML Schema - Cant generate correct number of rows

dataservices realtime
#1

At first hello to everybody and sorry in advance but iam no native english speaker.

I bang my Head against this little problem the last few days and doesnt know how to solve this.

Lets explain the following Situation

We have some Flat Test Data in the following format. (Sorry for bad formatting)

classtype - category - article

  5000        AAA       1001
  5000        AAA       1002
  5000        BBB       1003
  5000        BBB       1004
  5000        BBB       1005
  5000        CCC       1006

And a XML Schema with this format


Root
  |
  --- Classtype
           |
           --- Category
                    |
                    --- Article

-----------------

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Root">
	<xs:complexType>
		<xs:sequence>
			<xs:element name="Classtype">
				<xs:complexType>
					<xs:sequence>
						<xs:element name="NameClasstype" type="xs:string"/>
						<xs:element name="Category">
							<xs:complexType>
								<xs:sequence>
									<xs:element name="NameCategory" type="xs:string"/>
									<xs:element name="CategoryInformation">
										<xs:complexType>
											<xs:sequence>
												<xs:element name="NameOfCategory" type="xs:string"/>
												<xs:element name="NameArticle">
													<xs:complexType>
														<xs:simpleContent>
															<xs:extension base="xs:string">
																<xs:attribute name="ArticleAttribute" type="xs:string"/>
															</xs:extension>
														</xs:simpleContent>
													</xs:complexType>
												</xs:element>
											</xs:sequence>
										</xs:complexType>
									</xs:element>
								</xs:sequence>
							</xs:complexType>
						</xs:element>
					</xs:sequence>
				</xs:complexType>
			</xs:element>
		</xs:sequence>
	</xs:complexType>
</xs:element>		
</xs:schema>

This Schema Translate to this Nested XML in Data Services

The following images shows my Dataflow

I got 3 Distinct Querrys

D_Classtype - Select Distinct Classtype
D_Category_Classtype - Select Distinct Category and Classtype
D_Article_Category - Select Distinct Article and Catagory

I try to Map thes Querys with a Row Generation in den MAP Query to reach the following Goal:

One Root Note per XML Document

  • Row_Generator in From

One Classtype per Distinct Classtype Value

  • Use D_Classtype in From

One Category per Distinct Category Value matching the Classtype

  • Use D_Catagory_Classtype in From
  • Set Where Clause to --> D_Category_Classtype.Classtype = D_Classtype.Classtype[/list]

One CatagoryInformation per Category

  • Here is my Problem i dont know how to map this correcty. I would like to read the current Value of Category and use it in the where clause…
  • In the following Example Output i use D_Article_Category in FROM and --> D_Category_Classtype.Category = D_Article_Category.Category in the where clause

Alle Article to the corresponding Catagory

  • Use D_Article_Category in FROM
  • Use D_Article_Category.Category = D_Category_Classtype.Category in Where

So i got the following Output

1 x Root -> correct
1 x Classtype -> correct
3 x Category -> correct, but will be uncorrect if there will be more then one classtype
N x CategoryInformation -> I will get n Category Informations. One for every Article in the Category

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Classtype><NameClasstype>5000</NameClasstype>
<Category><NameCategory>AAA</NameCategory>
<CategoryInformation><NameOfCategory>AAA</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryAAA" >1001</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryAAA" >1002</NameArticle>
</CategoryInformation>
<CategoryInformation><NameOfCategory>AAA</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryAAA" >1001</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryAAA" >1002</NameArticle>
</CategoryInformation>
</Category>
<Category><NameCategory>BBB</NameCategory>
<CategoryInformation><NameOfCategory>BBB</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1003</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1004</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1005</NameArticle>
</CategoryInformation>
<CategoryInformation><NameOfCategory>BBB</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1003</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1004</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1005</NameArticle>
</CategoryInformation>
<CategoryInformation><NameOfCategory>BBB</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1003</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1004</NameArticle>
<NameArticle ArticleAttribute = "Iam in CategoryBBB" >1005</NameArticle>
</CategoryInformation>
</Category>
<Category><NameCategory>CCC</NameCategory>
<CategoryInformation><NameOfCategory>CCC</NameOfCategory>
<NameArticle ArticleAttribute = "Iam in CategoryCCC" >1006</NameArticle>
</CategoryInformation>
</Category>
</Classtype>
</Root>

Can anyone give me a tip how to produce a corret Mapping for this Case

BinDerKleineMann (BOB member since 2014-07-31)