Dataset from XML file

Discussion in 'ASP .Net' started by tshad, Dec 27, 2007.

  1. tshad

    tshad Guest

    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
    tshad, Dec 27, 2007
    #1
    1. Advertising

  2. 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" wrote:

    > 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], Dec 27, 2007
    #2
    1. Advertising

  3. tshad

    tshad Guest

    "Peter Bromberg [C# MVP]" <> wrote in message
    news:...
    > 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
    > -- Peter
    > Site: http://www.eggheadcafe.com
    > UnBlog: http://petesbloggerama.blogspot.com
    > MetaFinder: http://www.blogmetafinder.com
    >
    >
    > "tshad" wrote:
    >
    >> 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
    >>
    >>
    >>
    >>
    tshad, Dec 28, 2007
    #3
  4. tshad

    sloan Guest

    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.

    ...





    "Peter Bromberg [C# MVP]" <> wrote in message
    news:...
    > 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" wrote:
    >
    >> 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
    >>
    >>
    >>
    >>
    sloan, Dec 28, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nedu N
    Replies:
    1
    Views:
    1,772
    Chris Taylor
    Oct 31, 2003
  2. Replies:
    0
    Views:
    422
  3. jkflens
    Replies:
    2
    Views:
    1,444
    jkflens
    May 30, 2006
  4. ringo
    Replies:
    0
    Views:
    156
    ringo
    Aug 18, 2004
  5. Erik Wasser
    Replies:
    5
    Views:
    426
    Peter J. Holzer
    Mar 5, 2006
Loading...

Share This Page