By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

Parse Nested Elements to Single DataGrid

P: n/a
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
programmatically is killing me.

Getting the parent node to bind to the DataGrid is simple

dsSource.ReadXml(CStr(Session("FilePath")))

'Bind the XML to a DataGrid
dgMaster.DataSource = dsSource
dgMaster.DataBind()
dgMaster.Visible = 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.
Jun 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hi,

Is the Xml document generated by DataSet.WriteXml? 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.)

Jun 19 '06 #2

P: n/a
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 programmatically 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.WriteXml? 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.)

Jun 19 '06 #3

P: n/a
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.)

Jun 20 '06 #4

P: n/a
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.)

Jun 20 '06 #5

P: n/a
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.)

Jun 21 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.