Dataset from XML file

[tshad]

I have a table I am getting from an outside program that I am trying to read
to build some Sql Tables and insert data from.

The problem is that the following table puts all the tags together, instead
of separting them by the different forms.

******************************************************************
<Report>
<Appr>
<data>
<form name="order" primary="false">
<tag name="NAME" flags="1" format="4096">Michael Jones</tag>
<tag name="SIGNEDDATE" flags="1" format="4096">12/12/2007</tag>
<tag name="CLIENTADDRESS" flags="1" format="4096">1340 Winde Drive,
Tustin, CA 92660</tag>
</form>
<form name="title" primary="false">
<tag name="APPR_NAME" flags="1" format="12288">John Doe</tag>
<tag name="DATE" flags="1" format="12288">December 12, 2007</tag>
<tag name="NAME.1" flags="1" format="12288">Clark Kent</tag>
</form>
<form name="10013" primary="true">
<section type="subject" number="0">
<tag name="CITY" flags="1" format="4096">Irvine</tag>
<tag name="STATE" flags="1" format="4096">CA</tag>
</section>
<section type="sales" number="1">
<tag name="AGE" flags="1" format="4096">35</tag>
<tag name="SITE" flags="1" format="4096">12000</tag>
</section>
<section type="sales" number="2">
<tag name="AGE" flags="1" format="4096">34</tag>
<tag name="SITE" flags="1" format="4096">15000</tag>
</section>
</form>
</data>
</Appr>
</Report>
***********************************************

This is in essence what Excel does. I can go through the file pretty easily
and tell which form a tag is with.
*******************************************************
/Report
/Appr/data/form/#id /Appr/data/form/@name /Appr/data/form/@primary
/Appr/data/form/section/#id /Appr/data/form/section/@number
/Appr/data/form/section/@type /Appr/data/form/section/tag
/Appr/data/form/section/tag/@flags /Appr/data/form/section/tag/@format
/Appr/data/form/section/tag/@name /Appr/data/form/tag
/Appr/data/form/tag/@flags /Appr/data/form/tag/@format
/Appr/data/form/tag/@name
1 order FALSE Michael Jones 1 4096 NAME
1 order FALSE 12/12/2007 1 4096 SIGNEDDATE
1 order FALSE 1340 Winde Drive, Tustin, CA 92660 1 4096 CLIENTADDRESS
2 title FALSE John Doe 1 12288 APPR_NAME
2 title FALSE December 12, 2007 1 12288 DATE
2 title FALSE Clark Kent 1 12288 NAME.1
3 10013 TRUE 1 0 subject Irvine 1 4096 CITY
3 10013 TRUE 1 0 subject CA 1 4096 STATE
3 10013 TRUE 2 1 sales 35 1 4096 AGE
3 10013 TRUE 2 1 sales 12000 1 4096 SITE
3 10013 TRUE 3 2 sales 34 1 4096 AGE
3 10013 TRUE 3 2 sales 15000 1 4096 SITE
*******************************************************

But if I try to read them into a DataSet using ds.ReadXml, I get something
like the following:
******************************************************
Tablename = Appr
Tablename = data
Tablename = form
Number of rows = 3 and number of columns = 4
ColumnName = form_Id
ColumnName = name
ColumnName = primary
ColumnName = data_Id
Row: 0: 0 order false 0
Row: 1: 1 title false 0
Row: 2: 2 10013 true 0
Tablename = section
Tablename = tag
Number of rows = 12 and number of columns = 6
ColumnName = name
ColumnName = flags
ColumnName = format
ColumnName = tag_Text
ColumnName = section_Id
ColumnName = form_Id
Row: 0: NAME 1 4096 Michael Jones 0
Row: 1: SIGNEDDATE 1 4096 12/12/2007 0
Row: 2: CLIENTADDRESS 1 4096 1340 Winde Drive, Tustin, CA
92660 0
Row: 3: APPR_NAME 1 12288 John Doe 1
Row: 4: DATE 1 12288 December 12, 2007 1
Row: 5: NAME.1 1 12288 Clark Kent 1
Row: 6: CITY 1 4096 Irvine 0
Row: 7: STATE 1 4096 CA 0
Row: 8: AGE 1 4096 35 1
Row: 9: SITE 1 4096 12000 1
Row: 10: AGE 1 4096 34 2
Row: 11: SITE 1 4096 15000 2
********************************************************

All the form names are together and all the tag names are together, but I
need to have it work similar to the excel file where the Form is the table
and the Sections, numbers and Tags are rows in the table.

Is there a way to easily read the data into a dataset and have it do
something like that?

I assume I would have to set up some type of schema. But I am not sure how
to easily do this.

Thanks,

Tom

 

[Peter Bromberg [C# MVP]]

Aside from having any luck experimenting with the various XmlReadMode
enumerations, you are probably facing a situation where you're going to have
to perform an xsl transform on the existing Xmldocument to a new one in order
to get what you want into the DataSet.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com

 

[tshad]

Aside from having any luck experimenting with the various XmlReadMode
enumerations, you are probably facing a situation where you're going to
have
to perform an xsl transform on the existing Xmldocument to a new one in
order
to get what you want into the DataSet.

How would I do that?

Do you know where thereis a good article or 2 on that? Both the
XmlReadModes and xsl transformations?

Thanks,

Tom

 

[sloan]

This guy is double dipping: (3 minutes post date).

http://groups.google.com/group/micr...read/thread/6229fb2fb84d7381/801786c366557e75


I've responded to your post in the vb.net group.

Please do NOT multi post. It wastes people's time.

I have already provided an xml to xml tranformation link/article for you.

...