Dataset from XML file
tshad
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
sloan
Im not sure what you're doing exactly..but.
This is how I run the circle.
I create a strongly typed dataset.
I throw a few dummy rows into it via code.
I call the ds.SaveXml( "C:\myfile.xml") method.
I open it up in notepad, and take a look.
.......
Now, you can check this article out:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry
and see if that helps.
But I'd spend a little time figuring out how to create a strongly typed
dataset...and work from there.
..
"tshad" <t...@dslextreme.com> wrote in message
news:e9JEWbMS...@TK2MSFTNGP02.phx.gbl...
tshad
"sloan" <sl...@ipass.net> wrote in message
news:O3TBkPNS...@TK2MSFTNGP03.phx.gbl...
>
> Im not sure what you're doing exactly..but.
>
I am taking an xml file that is given to my by a client that is created by
their 3rd party software and creating some tables from it to put into our
database.
Instead of reading the xml file line by line, I am trying to read to read
the data into a dataset and then handling the data in the dataset.
The problem is the data (tags) is not being separated by form but being put
together in a file called Tag. In the excel file, it puts the data together
and you can see each row separated by form, section and number.
> This is how I run the circle.
>
> I create a strongly typed dataset.
Not sure what you mean by strongly typed dataset?
>
> I throw a few dummy rows into it via code.
>
> I call the ds.SaveXml( "C:\myfile.xml") method.
>
> I open it up in notepad, and take a look.
>
> .......
>
> Now, you can check this article out:
> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry
I will check this out. It is not written in vb.net so I will need to change
it so that I can see better what it is doing.
Thanks,
Tom
Cor Ligthert[MVP]
Be aware that a dataset is written as XML file, therefore not every XML file
is a dataset.
XML is a free code instead of HTML, you can use your own elements and
attributes to create a datacollection of everything even if that is a
program. Therefore it is not direct a kind of datacontainer, it can be that
like the dataset.
There are tools as included in visual studio to make from most (data) XML
files dataset. This means they would have some basics to make this possible.
However, a SQL based Database is exist only from tables with a lot of
indexes. You can compare a table from that with the old type of carton
telephone registers however with more tabs.
Therefore a dataset can only exist from elements. If you try the drag and
drop tool which is in Visual Studio than it creates a from the XML file an
XSD datasetdescription (again a XML file). You can use that XSD file to
genereate a strongly typed datasetclass which is (AFAIK I don't remember me
if I ever tried that) to access your XML file.
I know however no method on the fly to do that, then simply reading the file
with the XMLnodeReader.
While of course version VB 2008 (and not C# 2008) has great tools to use for
you using Linq to XML. (Just search MSDN for that, it is for me too to fresh
to write here about it now already).
Cor
sloan
My sample doesn't really have any c# code either.
Its just an xsl and a xml....and a vbs to put the two together.
The only code I had was 2 lines of C#.
Here is the translation:
Dim ds As MyStronglyTypedDS = New MyStronglyTypedDS()
ds.ReadXml("c:\mypath\results.xml")
Here is the vb.net version of the ms kb I mention:
http://support.microsoft.com/kb/309702/en-us
//Quote
Not sure what you mean by strongly typed dataset?
//End Quote
http://www.google.com/search?source=ig&hl=en&rlz=&q=strongly+typed+dataset&btnG=Google+Search
You really ought to investigate a untyped vs typed (or strong) dataset.
Good luck.
"tshad" <t...@dslextreme.com> wrote in message
news:OC7NyUSS...@TK2MSFTNGP02.phx.gbl...
tshad
news:0450E9B4-5BCA-40C1...@microsoft.com...
> Thad,
>
> Be aware that a dataset is written as XML file, therefore not every XML
> file is a dataset.
>
I understand that every XML file is not a dataset, which is my problem.
I may have to run this without using a data set as I can't figure out how to
get it to format the results similar to the way that XML does it.
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
In the above it seems to separate the Form tags as 1, 2 and 3 in the first
column and then the column name. There are no sections or section numbers
in the first 2 forms.
The Form type 3 does have sections and sections numbers (also 1, 2 and 3).
If I could make my dataset put all the data in one table like this, that
would solve my problem. I am looking at setting up a schema to do it and
tried running xsd.exe on it and got the following:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="Report" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="tag" nillable="true">
<xs:complexType>
<xs:simpleContent msdata:ColumnName="tag_Text" msdata:Ordinal="3">
<xs:extension base="xs:string">
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="flags" type="xs:string" />
<xs:attribute name="format" type="xs:string" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="Report" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="tag" />
<xs:element name="Appr">
<xs:complexType>
<xs:sequence>
<xs:element name="data" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="form" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="section" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element ref="tag" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="type" type="xs:string" />
<xs:attribute name="number" type="xs:string"
/>
</xs:complexType>
</xs:element>
<xs:element ref="tag" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="primary" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Which won't work.
Is there a way to change this to come up with one table and put the data in
the table like the excel file with columns something like:
FormID, FormName, Section,SectionNumber,...
Thanks,
Tom
Cor Ligthert[MVP]
You can never use attributes in a dataset. The Dataset follows the SQL types
DataBases, which does only know tables and rows with fields, where in fact
rows have therefore ontly elements. Therefore is this translation from
attributes in elements to tables with elements.
Cor
"tshad" <t...@dslextreme.com> schreef in bericht
news:uqM9d63S...@TK2MSFTNGP06.phx.gbl...
Cor Ligthert[MVP]
However, probably you can use this one, I almost forgot it, I have the idea
that it is very much in line with your question (not simple overtyping).
http://msdn2.microsoft.com/en-us/library/7sfkwf9s(vs.71).aspx
Cor
"Cor Ligthert[MVP]" <notmyfi...@planet.nl> schreef in bericht
news:5C6297C3-148B-4F8F...@microsoft.com...