Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating multiple repeating groups within another repeating group Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 7:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 2:44 AM
Points: 61, Visits: 194
Hi,

I have some XML like the following

<Root>
<Meters>
<Meter>
<Reads>
<Read>...</Read>
<Read>...</Read>
<Read>...</Read>
</Reads>
<LineItems>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
</LineItems>
</Meter>

<Meter>
<Reads>
<Read>...</Read>
<Read>...</Read>
<Read>...</Read>
</Reads>
<LineItems>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
<LineItem>...</LineItem>
</LineItems>
</Meter>
</Meters>
</Root>

So for each <Meters> node, I can have multiple <Meter> nodes, for each <Reads> node, I can have multiple <Read> nodes, for each <LineItems> node, I can have multiple <LineItem> nodes. The contents within a <Read> node is completely different from the contents within a

I am trying to convert this XML so that it can be displayed in a tablix, where the table grouping is on the meter node (so for each meter, this will create a row in the tablix), and for each row in the table, then I was looking to have this do its own grouping within it, with the <Reads> node being its own table within the tablix, that expands, depending on how many of the <Read> nodes there are, and then on the same level, then another grouping in another tablix for the <LineItems>, which will expand depending on how many <LineItem> nodes there are.
For each <Meter> node, there will only be one <Reads> and <LineItems> node.
I imagine that the tablix for the <LineItem> and the tablix for the <Read> nodes are both within a cell of the tablix for the <Meter> nodes, so it is basically a repeating group, and within it, I have two more repeating groups.

Is this possible? It sounds possible in my head, but the main roadblock in my way is how do I create a single dataset from the XML that can do this? It needs to be a single dataset, so that I can do the grouping on the meter node I think? I wouldn't be able to do grouping on disparate datasets.
I can take blocks of XML that can be represented by a block by using the
<Query><XMLData>...</XMLData><ElementPath>...</ElementPath></Query> method, but if it is possible to use this to extract data out of a data source where the XML datasource is more complex.

If this is possible, does anyone have any examples of this being done?

Any help would be extremely welcomed.
Post #962197
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse