• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

save / reload datatable with sql server 2005 xml datatype

I'm trying to save a datatable into a sql 2005 xml field and then reload it later into a datatable.

Can't seem to find a method that works.

Here is my code to save:
***********************************************************
        Dim StringWriter As New System.IO.StringWriter
        datatable.WriteXml(StringWriter)
        Dim conn As New SqlConnection(ShortCuts.ConnectionString)
        Dim cmd As New SqlCommand("sSaveSavedStoredGroup", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Name", saveName)
        cmd.Parameters.AddWithValue("@data", StringWriter.ToString())


It produces the following info in sql:
***********************************************************
<DocumentElement>
  <Table1>
    <idAccount>2351</idAccount>
    <sessionName>Adventure Camp</sessionName>
    <name>ashley, joelle</name>
    <idSession>3949</idSession>
    <age>56</age>
    <gender>Female</gender>
    <idSessionType>1</idSessionType>
    <idSessionAssignment>110481</idSessionAssignment>
    <orderDate>2006-09-03T16:35:41.707-04:00</orderDate>
    <confirmed>true</confirmed>
    <Cabin>?</Cabin>
    <madeBy>Staff</madeBy>
    <idPerson>96033</idPerson>
    <sessionBalance>200.00</sessionBalance>
    <arrived>false</arrived>
  </Table1>
  <Table1>
    <idAccount>42</idAccount>
    <sessionName>Adventure Camp</sessionName>
    <name>AshleyAdded, Daniel</name>
    <idSession>3949</idSession>
    <age>11</age>
    <gender>Male</gender>
    <idSessionType>1</idSessionType>
    <idSessionAssignment>110573</idSessionAssignment>
    <orderDate>2006-11-10T14:19:16.24-05:00</orderDate>
    <confirmed>true</confirmed>
    <Cabin>?</Cabin>
    <madeBy>Staff</madeBy>
    <idPerson>90037</idPerson>
    <sessionBalance>110.00</sessionBalance>
    <arrived>false</arrived>
  </Table1>
  <Table1>
    <idAccount>4711</idAccount>
    <sessionName>Adventure Camp</sessionName>
    <name>test, test</name>
    <idSession>3949</idSession>
    <age>5</age>
    <gender>Male</gender>
    <idSessionType>1</idSessionType>
    <idSessionAssignment>110567</idSessionAssignment>
    <orderDate>2006-10-18T23:17:32.14-04:00</orderDate>
    <confirmed>false</confirmed>
    <Cabin>?</Cabin>
    <madeBy>Staff</madeBy>
    <idPerson>255104</idPerson>
    <sessionBalance>200.00</sessionBalance>
    <arrived>false</arrived>
  </Table1>
</DocumentElement>

Here is the code I'm using to load
******************************************************
        Dim conn As New SqlConnection(ShortCuts.ConnectionString)
        Dim cmd As New SqlCommand("SELECT data FROM tbSavedStoredGroups WHERE idSavedStoredGroup = @idSavedStoredGroup", conn)
        cmd.Parameters.AddWithValue("@idSavedStoredGroup", Me.ddlGroups.SelectedValue)
        conn.Open()
        Dim dt As New DataTable
        dt.Load(cmd.ExecuteReader)

******************************************

When I load it back it returns a one column - and one row with all the data running together sperated by lots of diferent charaters.

What am I doing wrong?

Thanks,
Daniel
0
dbashley1
Asked:
dbashley1
1 Solution
 
gangwischCommented:
you need to have an xslt file on how to transform the data that you receive
0
 
dbashley1Author Commented:
gangwisch,

Thanks you pointed me in the right direction.

Here is the working code to save:
*******************************************
        Dim StringWriter As New System.IO.StringWriter
        datatable.WriteXml(StringWriter, XmlWriteMode.WriteSchema)
        Dim conn As New SqlConnection(ShortCuts.ConnectionString)
        Dim cmd As New SqlCommand("sSaveSavedStoredGroup", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Name", saveName)
        cmd.Parameters.AddWithValue("@data", StringWriter.ToString())


Here is the working code to load:
*******************************************
        Dim conn As New SqlConnection(ShortCuts.ConnectionString)
        Dim cmd As New SqlCommand("SELECT data FROM tbSavedStoredGroups WHERE idSavedStoredGroup = @idSavedStoredGroup", conn)
        cmd.Parameters.AddWithValue("@idSavedStoredGroup", Me.ddlGroups.SelectedValue)
        conn.Open()
        Dim dt As New DataTable
        dt.ReadXml(cmd.ExecuteXmlReader)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now