Parse Nested Elements to Single DataGrid

[BMeyer]

I have been losing my mind trying to parse an XML document (with nested child
elements, not all of which appear in each parent node) into a DataGrid
object. What I want to do is "flatten" the XML document into a text
document with a single row for each parent node (that has all of the values
from all of the child nodes for that row)

The DataView within VS 2005 IDE displays my 15 or so child tables - and
knows that some parent rows don't have child rows in every relation - but
trying to get all the child tables to go with the correct parent row
programmaticall y is killing me.

Getting the parent node to bind to the DataGrid is simple

dsSource.ReadXm l(CStr(Session( "FilePath") ))

'Bind the XML to a DataGrid
dgMaster.DataSo urce = dsSource
dgMaster.DataBi nd()
dgMaster.Visibl e = True

Of course, we all know that this will only show me dsSource.Tables (0), not
ALL of them

Isn't there an easy way to get the values from all the child nodes to bind
and display on the same DataGrid rather than one separate DataGrid for each
child relation?

Obviously, the DataView within the VS 2005 IDE understands how to infer the
relationships between parent and child nodes (I guess with ParentRelations
and ChildRelations objects - and associated ID's)

I know the DOM is supposed to be able to parse through child nodes, but how
do I do that and get them all linked up to the correct parent row? Right
now the code I have uses all kinds of row counters and all kinds of loops
similar to

For i = 0 To dsSource.Tables .Count - 1

Next

My row counters (and associated arrays to track CurrentRow, LastRow, etc)
are constantly off by one or two rows.

Help!!! I know I missing something, but I'm so far down the rat hole I
can't find my way back out.

[Kevin Yu [MSFT]]

Hi,

Is the Xml document generated by DataSet.WriteXm l? If so, have you tried to
create a schema, which contains DataRelation information and can create the
map automatically when reading from Xml.

Kevin Yu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== ===========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =============== =============== =
=============== ===========

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[BMeyer]

I doubt the document is generated by that method, since the website that
produces the XML file is a jsp based app. However, it sounds like your
suggestion might still be an option. (?) Is there any example out there on
MSDN that shows programmaticall y how to create the schema (and map the
information), since I need to automate the process?

Thanks.

"Kevin Yu [MSFT]" wrote:

Hi,

Is the Xml document generated by DataSet.WriteXm l? If so, have you tried to
create a schema, which contains DataRelation information and can create the
map automatically when reading from Xml.

Kevin Yu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== ===========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =============== =============== =
=============== ===========

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi,

There are several ways to achieve this.

If the schema is included in the Xml document, you can use
XmlReadMode.Rea dSchema directly to get the schema info.

If the Xml document doesn't include schema, you can use
XmlReadMode.Inf erSchema to let it generate schema for you. However, this is
not 100% reliable for the nested relationship in the table.

The other way is to design a typed DataSet, which contains DataRelation
info.

Kevin Yu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== ===========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =============== =============== =
=============== ===========

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[BMeyer]

There is no schema included, so it looks like the first step then would be to
use XmlReadMode.Inf erSchema.

Absent that, I've been doing this via an untyped dataset and parse each
child table. I'm going to post the code here, but it sounds like if I could
just find some way to create a typed dataset, I wouldn't need all this
looping and could instead somehow write all the data to a single DataSet that
would display "flat" in a single spreadsheet? Problem is, I cannot figure
out how to create a dataset that does NOT have the nested child relations as
different datasets.

Public Sub PopulateTable_d emo()

'Copied from PopulateTable
Dim i, j, l, x, xTableNum As Integer

'Dim MasterTable As New DataTable
Dim MyCol As DataColumn
Dim MyRow As DataRow
Dim TableRowNum(,) As String
Dim ColumnName As String
dsSource.ReadXm l(CStr(Session( "FilePath") ))

'Before we do ANYTHING, let's delete the PERSON table and all
its child tables from the dataset so we don't store personal info
ReDim TableRowNum(dsS ource.Tables.Co unt - 1, 2)
'Initialize all the values to zero
For i = 0 To dsSource.Tables .Count - 1

TableRowNum(i, 0) = 0
TableRowNum(i, 1) = dsSource.Tables (i).TableName
TableRowNum(i, 2) = 0

Next
'Create mastertable

For i = 0 To dsSource.Tables .Count - 1
For j = 0 To dsSource.Tables (i).Columns.Cou nt - 1

'Need to add two extra columns - 1 for lat, 1 for long
ColumnName = dsSource.Tables (i).TableName &
dsSource.Tables (i).Columns(j). ColumnName

If MasterTable.Col umns.IndexOf(Co lumnName) = -1 Then
'If
MasterTable.Col umns.IndexOf(ds Source.Tables(i ).Columns(j).Co lumnName) = -1
Then
'MyCol = New
DataColumn(dsSo urce.Tables(i). Columns(j).Colu mnName)
MyCol = New DataColumn(Colu mnName)
MyCol.DataType = System.Type.Get Type("System.St ring")
MasterTable.Col umns.Add(MyCol)

End If

Next
Next

'Now populate each row
Dim LoopRowNum
Dim PrimaryKeyName As String
Dim PrimaryColumnVa lue As Integer

Dim CellValue() As Object
Dim k, m, t As Integer
Dim TableName, TableNameInner As String
Dim TableEntry(,) As String
Dim ProcessTable As Boolean
Dim ParentTableName As String
Dim ParentKeyName As String
'This holds values for whether child table was checked already
for current row j
ReDim TableEntry(dsSo urce.Tables.Cou nt - 1, 1)
For j = 0 To dsSource.Tables (0).Rows.Count - 1

'Reinitialize TableEntry array - haven't hit any child
tables yet for this row
For t = 0 To dsSource.Tables .Count - 1
TableEntry(t, 0) = False
TableEntry(t, 1) = ""
Next

MyRow = MasterTable.New Row()

'Dont think I need this?
'Get value of parent id
'Counter of PrimaryKeyRow equals row of master table, which
is simply "j"

For l = 0 To dsSource.Tables (0).Columns.Cou nt - 1
ColumnName = dsSource.Tables (0).TableName &
dsSource.Tables (0).Rows(j).Tab le.Columns(l).C olumnName

'btm16jun-MyRow.Item(dsSo urce.Tables(0). Rows(j).Table.C olumns(l).Colum nName)
= dsSource.Tables (0).Rows(j).Ite m(l)
MyRow.Item(Colu mnName) =
dsSource.Tables (0).Rows(j).Ite m(l)

Next
'Save the next and last row values for this parent
TableRowNum(0, 0) = j + 1
TableRowNum(0, 2) = j

For i = 1 To dsSource.Tables .Count - 1
Dim ChildCount As Integer

CellValue = Nothing

TableName = dsSource.Tables (i).TableName

If
CStr(dsSource.T ables("Event"). Rows(j).Item("I nternalID")) = "9068" Then
If TableName = "VehiclesInvolv ed" Then
Dim test1 As String = "test"
End If
End If
'Always hit table 1, so skip this checking the
TableEntry Array

'Only hit this table if it hasn't been hit already for
this row
ProcessTable = True
For x = 1 To dsSource.Tables .Count - 1
'For m = 0 To dsSource.Tables .Count - 2
If TableEntry(x, 1) = TableName And
CBool(TableEntr y(x, 0)) = True Then
ProcessTable = False
Exit For
End If

Next
'end skip TableEntry Array

If ProcessTable = True Then
'Get LoopRowNum for table where TableName is the key
LoopRowNum = CInt(TableRowNu m(i, 0))

'Save off the current row value into i,2
TableRowNum(i, 2) = LoopRowNum

''Don't need this - we have the parent primary key...

'Gotta check to see who the parent table is...
ParentTableName =
dsSource.Tables (TableName).Par entRelations.It em(0).ParentTab le.TableName

ParentKeyName =
dsSource.Tables (TableName).Par entRelations.It em(0).ParentCol umns(0).ColumnN ame

'Get parent column value of parent table - have to
choose the right parent row num (TableRowNum(0, i))
For x = 0 To dsSource.Tables .Count - 1
If TableRowNum(x, 1) = ParentTableName Then
'PrimaryColumnV alue =
dsSource.Tables (ParentTableNam e).Rows(TableRo wNum(0, x)).Item(Parent KeyName)
'Maybe the 2,x goes here?
PrimaryColumnVa lue =
dsSource.Tables (ParentTableNam e).Rows(TableRo wNum(x, 2)).Item(Parent KeyName)
Exit For
End If
Next

'Now that we know name of primary key column, keep
checking it until row number changes
'Create Array to hold Nolumn values - appending
multiple row values - in each cell
If LoopRowNum <
dsSource.Tables (TableName).Row s.Count Then
ReDim
CellValue(dsSou rce.Tables(Tabl eName).Columns. Count - 1)

'btm16jun-If
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me).GetType.Nam e
<> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me).GetType.Nam e
= "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me)
End If
Do Until LoopVal <> PrimaryColumnVa lue
'Do Until
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me) <>
PrimaryColumnVa lue

For l = 0 To
dsSource.Tables (TableName).Col umns.Count - 1
If
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(l).GetType. Name() = "DBNull"
Then
CellValue(l) = CellValue(l) & ""
Else
CellValue(l) = CellValue(l) &
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(l) & vbCrLf
End If

Next

'Save off tableRowNum in case we just hit
the last row...

'now increment it
LoopRowNum = LoopRowNum + 1

'Save off tableRowNum in case we just hit
the last row...
TableRowNum(i, 0) = LoopRowNum

'Only keep looping if we're not at max row
count
If LoopRowNum =
dsSource.Tables (TableName).Row s.Count Then
Exit Do
End If

'2.0-BTM - might also be a null now that
were looking at next row, if so, exit?
If
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me).GetType.Nam e
= "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables (TableName).Row s(LoopRowNum).I tem(ParentKeyNa me)

Loop

'Flag TableEntry (for TableName) - as being
complete for this row - don't come back to this table again until next master
row (j)
TableEntry(i, 0) = True
TableEntry(i, 1) = TableName

'Add the items to the row - if we had any rows
to add
If LoopRowNum > CInt(TableRowNu m(i, 2)) Then

For l = 0 To
dsSource.Tables (TableName).Col umns.Count - 1

'Again, row(0) for getting the column
name is fine
ColumnName = TableName &
dsSource.Tables (TableName).Row s(0).Table.Colu mns(l).ColumnNa me

'btm16Jun-MyRow.Item(dsSo urce.Tables(Tab leName).Rows(0) .Table.Columns( l).ColumnName) = CellValue(l)
MyRow.Item(Colu mnName) = CellValue(l)

Next
End If
'btm-16Jun-End If
End If

Else
'we have nothing to add to this table since were
past the last row, just flag as complete so we don't hit it again
TableEntry(i, 0) = True
TableEntry(i, 1) = TableName

End If

''put new end if here

'Now see if there are child tables to Table(1)

'If count is zero, then parent is Table(0), process row
now
If dsSource.Tables (TableName).Chi ldRelations.Cou nt > 0
Then

ChildCount =
dsSource.Tables (TableName).Chi ldRelations.Cou nt - 1
'First get value of ParentColumn ID

PrimaryKeyName =
dsSource.Tables (TableName).Chi ldRelations.Ite m(0).ParentColu mns(0).ColumnNa me

''''Get value that is in the parent column
''''Get PrimaryKeyValue - have to choose the current
row num (current value in TableRowNum(0,x ))
For x = 0 To dsSource.Tables .Count - 1
If TableRowNum(x, 1) = TableName Then
'Again, we may already have processed last
row in this table, if so, don't go get primary column - as we shouldn't be
processing any more child rows either
If TableRowNum(x, 2) <
dsSource.Tables (TableName).Row s.Count Then
PrimaryColumnVa lue =
dsSource.Tables (TableName).Row s(TableRowNum(x , 2)).Item(Primar yKeyName)
End If

Exit For
End If
Next

For k = 0 To ChildCount

TableNameInner =
dsSource.Tables (TableName).Chi ldRelations.Ite m(k).ChildTable .TableName

'Only hit this table if it hasn't been hit
already for this row
ProcessTable = True
For x = 1 To dsSource.Tables .Count - 1
'For m = 0 To dsSource.Tables .Count - 2
If TableEntry(x, 1) = TableNameInner And
CBool(TableEntr y(x, 0)) = True Then
ProcessTable = False
Exit For
End If

Next

If ProcessTable = True Then

CellValue = Nothing
'Here, we're not necessarily on Table(i), so
ref by tablename instead
For x = 1 To dsSource.Tables .Count - 1
If TableRowNum(x, 1) = TableNameInner Then
xTableNum = x
Exit For
End If
Next

LoopRowNum = CInt(TableRowNu m(xTableNum, 0))

'Again, save current row in x,2
TableRowNum(xTa bleNum, 2) = LoopRowNum

'Get primary key column name for reference
'Now that we know name of primary key
column, keep checking it until row number changes
'Create Array to hold Nolumn values -
appending multiple row values - in each cell

ParentKeyName =
dsSource.Tables (TableNameInner ).ParentRelatio ns.Item(0).Pare ntColumns(0).Co lumnName

If LoopRowNum <
dsSource.Tables (TableNameInner ).Rows.Count Then
ReDim
CellValue(dsSou rce.Tables(Tabl eNameInner).Col umns.Count - 1)
'btm16jun-If
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(Parent KeyName).GetTyp e.Name <> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(Parent KeyName).GetTyp e.Name = "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(Parent KeyName)
End If
Do Until LoopVal <> PrimaryColumnVa lue

If LoopRowNum =
dsSource.Tables (TableNameInner ).Rows.Count Then
Exit Do
End If

For l = 0 To
dsSource.Tables (TableNameInner ).Columns.Count - 1
If
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(l).Get Type.Name() =
"DBNull" Then
CellValue(l) = CellValue(l)
& ""
Else
CellValue(l) = CellValue(l)
& dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(l) & vbCrLf
End If
Next

LoopRowNum = LoopRowNum + 1

TableRowNum(xTa bleNum, 0) = LoopRowNum
'Only keep looping if we're not at
max row count
If LoopRowNum =
dsSource.Tables (TableNameInner ).Rows.Count Then
Exit Do

End If

'2.0-BTM - might also be a null now
that were looking at next row, if so, exit?
If
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(Parent KeyName).GetTyp e.Name = "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables (TableNameInner ).Rows(LoopRowN um).Item(Parent KeyName)
Loop
'Save off row counter of that table to
array again - start there next time for master row (j)

'Flag TableEntry (for TableName) - as
being complete for this row - don't come back to this table again until next
master row (j)
TableEntry(xTab leNum, 0) = True
TableEntry(xTab leNum, 1) = TableNameInner

'Add the items to the row - if we had
any rows to add
If LoopRowNum >
CInt(TableRowNu m(xTableNum, 2)) Then
'Add the items to the row
For l = 0 To
dsSource.Tables (TableNameInner ).Columns.Count - 1

'Again, row(0) for getting the
column name is fine
ColumnName = TableNameInner &
dsSource.Tables (TableNameInner ).Rows(0).Table .Columns(l).Col umnName

'btm16Jun-MyRow.Item(dsSo urce.Tables(Tab leNameInner).Ro ws(0).Table.Col umns(l).ColumnN ame) = CellValue(l)
MyRow.Item(Colu mnName) =
CellValue(l)

Next
End If
'End If
'btm16Jun-end
Else
'were already past last row of this
table, be sure to flag as complete so we don't hit it again
TableEntry(xTab leNum, 0) = True
TableEntry(xTab leNum, 1) = TableNameInner
End If

'End If
'btm16Jun end

End If

Next

End If
''end fix attempt

Next
MasterTable.Row s.Add(MyRow)
Next

BindDataGrid()
End Sub

"Kevin Yu [MSFT]" wrote:

Hi,

There are several ways to achieve this.

If the schema is included in the Xml document, you can use
XmlReadMode.Rea dSchema directly to get the schema info.

If the Xml document doesn't include schema, you can use
XmlReadMode.Inf erSchema to let it generate schema for you. However, this is
not 100% reliable for the nested relationship in the table.

The other way is to design a typed DataSet, which contains DataRelation
info.

Kevin Yu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== ===========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =============== =============== =
=============== ===========

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi,

I'm not quite sure what you mean by createing a dataset that "does NOT have
the nested child relations as different datasets". I think creating a
DataSet with nested relation is just fine and you can read xml data into
that dataset. Then the whole structure will be created.

Kevin Yu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== ===========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =============== =============== =
=============== ===========

(This posting is provided "AS IS", with no warranties, and confers no
rights.)