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).Col umns(j).ColumnName) = -1
Then
'MyCol = New
DataColumn(dsSource.Tables(i).Columns(j).ColumnNam e)
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).Column Name
'btm16jun-MyRow.Item(dsSource.Tables(0).Rows(j).Table.Column s(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("Intern alID")) = "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(P arentKeyName).GetType.Name
<> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables(TableName).Rows(LoopRowNum).Item(P arentKeyName).GetType.Name
= "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables(TableName).Rows(LoopRowNum).Item(P arentKeyName)
End If
Do Until LoopVal <> PrimaryColumnValue
'Do Until
dsSource.Tables(TableName).Rows(LoopRowNum).Item(P arentKeyName) <>
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(P arentKeyName).GetType.Name
= "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables(TableName).Rows(LoopRowNum).Item(P arentKeyName)
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).Tabl e.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.It em(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).I tem(ParentKeyName).GetType.Name <> "DBNull" Then
Dim LoopVal As Integer
If
dsSource.Tables(TableNameInner).Rows(LoopRowNum).I tem(ParentKeyName).GetType.Name = "DBNull" Then
LoopVal = 0
Else
LoopVal =
dsSource.Tables(TableNameInner).Rows(LoopRowNum).I tem(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).I tem(l).GetType.Name() =
"DBNull" Then
CellValue(l) = CellValue(l)
& ""
Else
CellValue(l) = CellValue(l)
& dsSource.Tables(TableNameInner).Rows(LoopRowNum).I tem(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).I tem(ParentKeyName).GetType.Name = "DBNull" Then
Exit Do
End If
LoopVal =
dsSource.Tables(TableNameInner).Rows(LoopRowNum).I tem(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.Colu mns(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.)