RE: Parse Nested Elements to Single DataGrid

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

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_demo()

'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.ReadXml(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(dsSource.Tables.Count - 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.Count - 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.Columns.IndexOf(ColumnName) = -1 Then
'If
MasterTable.Columns.IndexOf(dsSource.Tables(i).Columns(j).ColumnName) = -1
Then
'MyCol = New
DataColumn(dsSource.Tables(i).Columns(j).ColumnName)
MyCol = New DataColumn(ColumnName)
MyCol.DataType = System.Type.GetType("System.String")
MasterTable.Columns.Add(MyCol)

End If

Next
Next

'Now populate each row
Dim LoopRowNum
Dim PrimaryKeyName As String
Dim PrimaryColumnValue 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(dsSource.Tables.Count - 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.NewRow()

'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.Count - 1
ColumnName = dsSource.Tables(0).TableName &
dsSource.Tables(0).Rows(j).Table.Columns(l).ColumnName

'btm16jun-MyRow.Item(dsSource.Tables(0).Rows(j).Table.Columns(l).ColumnName)
= dsSource.Tables(0).Rows(j).Item(l)
MyRow.Item(ColumnName) =
dsSource.Tables(0).Rows(j).Item(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.Tables("Event").Rows(j).Item("InternalID")) = "9068" Then
If TableName = "VehiclesInvolved" 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(TableEntry(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(TableRowNum(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).ParentRelations.Item(0).ParentTable.TableName

ParentKeyName =
dsSource.Tables(TableName).ParentRelations.Item(0).ParentColumns(0).ColumnName

'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
'PrimaryColumnValue =
dsSource.Tables(ParentTableName).Rows(TableRowNum(0, x)).Item(ParentKeyName)
'Maybe the 2,x goes here?
PrimaryColumnValue =
dsSource.Tables(ParentTableName).Rows(TableRowNum(x, 2)).Item(ParentKeyName)
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).Rows.Count Then
ReDim
CellValue(dsSource.Tables(TableName).Columns.Count - 1)

'btm16jun-If
dsSource.Tables(TableName).Rows(LoopRowNum).Item(ParentKeyName).GetType.Name
<> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables(TableName).Rows(LoopRowNum).Item(ParentKeyName).GetType.Name
= "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables(TableName).Rows(LoopRowNum).Item(ParentKeyName)
End If
Do Until LoopVal <> PrimaryColumnValue
'Do Until
dsSource.Tables(TableName).Rows(LoopRowNum).Item(ParentKeyName) <>
PrimaryColumnValue

For l = 0 To
dsSource.Tables(TableName).Columns.Count - 1
If
dsSource.Tables(TableName).Rows(LoopRowNum).Item(l).GetType.Name() = "DBNull"
Then
CellValue(l) = CellValue(l) & ""
Else
CellValue(l) = CellValue(l) &
dsSource.Tables(TableName).Rows(LoopRowNum).Item(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).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(TableName).Rows(LoopRowNum).Item(ParentKeyName).GetType.Name
= "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables(TableName).Rows(LoopRowNum).Item(ParentKeyName)

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(TableRowNum(i, 2)) Then

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

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

'btm16Jun-MyRow.Item(dsSource.Tables(TableName).Rows(0).Table.Columns(l).ColumnName) = CellValue(l)
MyRow.Item(ColumnName) = 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).ChildRelations.Count > 0
Then

ChildCount =
dsSource.Tables(TableName).ChildRelations.Count - 1
'First get value of ParentColumn ID

PrimaryKeyName =
dsSource.Tables(TableName).ChildRelations.Item(0).ParentColumns(0).ColumnName

''''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).Rows.Count Then
PrimaryColumnValue =
dsSource.Tables(TableName).Rows(TableRowNum(x, 2)).Item(PrimaryKeyName)
End If

Exit For
End If
Next



For k = 0 To ChildCount

TableNameInner =
dsSource.Tables(TableName).ChildRelations.Item(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(TableEntry(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(TableRowNum(xTableNum, 0))

'Again, save current row in x,2
TableRowNum(xTableNum, 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).ParentRelations.Item(0).ParentColumns(0).ColumnName

If LoopRowNum <
dsSource.Tables(TableNameInner).Rows.Count Then


ReDim
CellValue(dsSource.Tables(TableNameInner).Columns.Count - 1)


'btm16jun-If
dsSource.Tables(TableNameInner).Rows(LoopRowNum).Item(ParentKeyName).GetType.Name <> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables(TableNameInner).Rows(LoopRowNum).Item(ParentKeyName).GetType.Name = "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables(TableNameInner).Rows(LoopRowNum).Item(ParentKeyName)
End If
Do Until LoopVal <> PrimaryColumnValue

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(LoopRowNum).Item(l).GetType.Name() =
"DBNull" Then
CellValue(l) = CellValue(l)
& ""
Else
CellValue(l) = CellValue(l)
& dsSource.Tables(TableNameInner).Rows(LoopRowNum).Item(l) & vbCrLf
End If


Next

LoopRowNum = LoopRowNum + 1

TableRowNum(xTableNum, 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(LoopRowNum).Item(ParentKeyName).GetType.Name = "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables(TableNameInner).Rows(LoopRowNum).Item(ParentKeyName)
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(xTableNum, 0) = True
TableEntry(xTableNum, 1) = TableNameInner

'Add the items to the row - if we had
any rows to add
If LoopRowNum >
CInt(TableRowNum(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).ColumnName

'btm16Jun-MyRow.Item(dsSource.Tables(TableNameInner).Rows(0).Table.Columns(l).ColumnName) = CellValue(l)
MyRow.Item(ColumnName) =
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(xTableNum, 0) = True
TableEntry(xTableNum, 1) = TableNameInner
End If

'End If
'btm16Jun end

End If



Next

End If
''end fix attempt

Next
MasterTable.Rows.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.ReadSchema directly to get the schema info.

If the Xml document doesn't include schema, you can use
XmlReadMode.InferSchema 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.)


.

Relevant Pages

  • Re: Datenbank dynamisch anlegen/erweitern
    ... Datenbank die Sub "DBtest" auf, ... Dim cat As New ADOX.Catalog ... If Not FileExistsThen ... TableName As String) ...
    (microsoft.public.de.vb.datenbank)
  • RE: Re-run a Query Based on Previous Results
    ... Function fnProgenitor(Fsbill As String, ... Dim rs As DAO.Recordset ... The value for ID is the first Parent in Fsbill. ...
    (microsoft.public.access.queries)
  • Datenbank dynamisch anlegen/erweitern
    ... Ich erstelle mit unten stehenden Code eine Datenbank per ADOX und lege dort beispielsweise 2 Tabellen mit je 2 Feldern an. ... Dim cat As New ADOX.Catalog ... TableName As String) ... Dim Exists As Boolean ...
    (microsoft.public.de.vb.datenbank)
  • RE: Re-run a Query Based on Previous Results
    ... Public Function fnProgenitor(TableName as String, ... Dim rs As DAO.Recordset ... Dim varParentID As Variant ... I have a function I use to determine the ROOT LEVEL parent (the ...
    (microsoft.public.access.queries)
  • Re: parent column in child row
    ... You can do what you want by adding a calculated column to the child ... DataTable that contains and expression that references a parent row column. ... Dim cn As SqlConnection = New SqlConnection ...
    (microsoft.public.dotnet.framework.adonet)