"Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
news:7F720AE9-CDA3-4D48-B266-41BDE6AF2026@microsoft.com...
> I'm not really sure whether this is the correct group but here we go.
>
> The SQL 2005 supports a new datatype called xml.
>
> If I create a table adapter in Visual Studio 2005 for the standard
> insert/update/delete/select methods the table adapter generates a dataset
> with the dataset field value of string for the SQL datavalue of Xml.
>
> If I look at the properties there is no Xml option so I guess this is the
> closest match.
>
> If I then try to insert an Xml document into the database using the table
> adapter it appears to generate an error stating that it cant convert xml
> to
> string.
>
> I havnt provided a String so assume this must be within the table adapter
> itself ie attempting to take my Xml document and convert it into a string
> in
> order to insert in to the SQL Xml field.
>
> This being the case is it possible to insert Xml data into an SQL Xml
> field
> using a table adapter and if so how is it done.
>

"David Browne" wrote:

>
> "Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
> news:7F720AE9-CDA3-4D48-B266-41BDE6AF2026@microsoft.com...
> > I'm not really sure whether this is the correct group but here we go.
> >
> > The SQL 2005 supports a new datatype called xml.
> >
> > If I create a table adapter in Visual Studio 2005 for the standard
> > insert/update/delete/select methods the table adapter generates a dataset
> > with the dataset field value of string for the SQL datavalue of Xml.
> >
> > If I look at the properties there is no Xml option so I guess this is the
> > closest match.
> >
> > If I then try to insert an Xml document into the database using the table
> > adapter it appears to generate an error stating that it cant convert xml
> > to
> > string.
> >
> > I havnt provided a String so assume this must be within the table adapter
> > itself ie attempting to take my Xml document and convert it into a string
> > in
> > order to insert in to the SQL Xml field.
> >
> > This being the case is it possible to insert Xml data into an SQL Xml
> > field
> > using a table adapter and if so how is it done.
> >
>
> Using AdventureWorks and creating a DataSet with a TableAdapter for
> Sales.Indivitual turns out that the TableAdapter will just use a
> SqlParameter like this
>
> this._commandCollection[1].Parameters.Add(
>     new System.Data.SqlClient.SqlParameter(
>         "@Demographics",
>         SqlDbType.Xml,
>         2147483647,
>         ParameterDirection.Input,
>         0,
>         0,
>         "Demographics",
>         DataRowVersion.Current, false, null, "", "", ""));
>
> That parameter's value can be either a string or a XmlReader.
>
> EG
>
>
>       DataSet1TableAdapters.IndividualTableAdapter ta = new
> DataSet1TableAdapters.IndividualTableAdapter();
>       //
>       string xml = @"
>         <IndividualSurvey
> xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"">
>           <TotalPurchaseYTD>8248.99</TotalPurchaseYTD>
>           <DateFirstPurchase>2001-07-22Z</DateFirstPurchase>
>           <BirthDate>1966-04-08Z</BirthDate>
>           <MaritalStatus>M</MaritalStatus>
>           <YearlyIncome>75001-100000</YearlyIncome>
>           <Gender>M</Gender>
>           <TotalChildren>2</TotalChildren>
>           <NumberChildrenAtHome>0</NumberChildrenAtHome>
>           <Education>Bachelors </Education>
>           <Occupation>Professional</Occupation>
>           <HomeOwnerFlag>1</HomeOwnerFlag>
>           <NumberCarsOwned>0</NumberCarsOwned>
>           <CommuteDistance>1-2 Miles</CommuteDistance>
>         </IndividualSurvey>
>         ";
>       XmlDocument d = new XmlDocument();
>       d.LoadXml(xml);
>
>
>       ta.InsertQuery(
>         11000,
>         12731,
>         new System.Xml.XmlNodeReader(d),  //XmlReader
>         DateTime.Parse("10/19/2004 9:11:32 AM"));
>
>       ta.InsertQuery(
>         11000,
>         12731,
>         xml,  //string
>         DateTime.Parse("10/19/2004 9:11:32 AM"));
>
>
> David
>
>
>

"Martyn Fewtrell" wrote:

> Thanks for your reply David but I'm afraid its above me.
>
> I have an SQL database within which, one of the fields in one of the tables
> is designated the Xml datatype. I have a dataset which is generated from that
> table but the datatype on the same field in the dataset is showing up as
> String. There doesnt appear to be an option to set it to Xml or perhaps
> Object.
>
> When I offer the table adapter some data, intellisense in Visual Studio
> tells me I need to offer an object and it will accept an Xml document.
> However when I call the code the table adapter throws an exception.
>
> Object must implement IConvertible.
>
> InvalidCastException: Failed to convert parameter value from a XmlDocument
> to a String.]
>
> (There is a lot more to the exception by I believe this is the relevant bit.)
>
> As far as I can see the only part of the code that wants a String is the
> Visual Studio generated table adapter and i dont appear to be able to change
> this.
>
> I'm wondering whether the table adapter doesnt have the ability to handle an
> Xml datatype or am I missing something?
>
> --
> Regards
>
> Martyn Fewtrell
>
>
> "David Browne" wrote:
>
> >
> > "Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
> > news:7F720AE9-CDA3-4D48-B266-41BDE6AF2026@microsoft.com...
> > > I'm not really sure whether this is the correct group but here we go.
> > >
> > > The SQL 2005 supports a new datatype called xml.
> > >
> > > If I create a table adapter in Visual Studio 2005 for the standard
> > > insert/update/delete/select methods the table adapter generates a dataset
> > > with the dataset field value of string for the SQL datavalue of Xml.
> > >
> > > If I look at the properties there is no Xml option so I guess this is the
> > > closest match.
> > >
> > > If I then try to insert an Xml document into the database using the table
> > > adapter it appears to generate an error stating that it cant convert xml
> > > to
> > > string.
> > >
> > > I havnt provided a String so assume this must be within the table adapter
> > > itself ie attempting to take my Xml document and convert it into a string
> > > in
> > > order to insert in to the SQL Xml field.
> > >
> > > This being the case is it possible to insert Xml data into an SQL Xml
> > > field
> > > using a table adapter and if so how is it done.
> > >
> >
> > Using AdventureWorks and creating a DataSet with a TableAdapter for
> > Sales.Indivitual turns out that the TableAdapter will just use a
> > SqlParameter like this
> >
> > this._commandCollection[1].Parameters.Add(
> >     new System.Data.SqlClient.SqlParameter(
> >         "@Demographics",
> >         SqlDbType.Xml,
> >         2147483647,
> >         ParameterDirection.Input,
> >         0,
> >         0,
> >         "Demographics",
> >         DataRowVersion.Current, false, null, "", "", ""));
> >
> > That parameter's value can be either a string or a XmlReader.
> >
> > EG
> >
> >
> >       DataSet1TableAdapters.IndividualTableAdapter ta = new
> > DataSet1TableAdapters.IndividualTableAdapter();
> >       //
> >       string xml = @"
> >         <IndividualSurvey
> > xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"">
> >           <TotalPurchaseYTD>8248.99</TotalPurchaseYTD>
> >           <DateFirstPurchase>2001-07-22Z</DateFirstPurchase>
> >           <BirthDate>1966-04-08Z</BirthDate>
> >           <MaritalStatus>M</MaritalStatus>
> >           <YearlyIncome>75001-100000</YearlyIncome>
> >           <Gender>M</Gender>
> >           <TotalChildren>2</TotalChildren>
> >           <NumberChildrenAtHome>0</NumberChildrenAtHome>
> >           <Education>Bachelors </Education>
> >           <Occupation>Professional</Occupation>
> >           <HomeOwnerFlag>1</HomeOwnerFlag>
> >           <NumberCarsOwned>0</NumberCarsOwned>
> >           <CommuteDistance>1-2 Miles</CommuteDistance>
> >         </IndividualSurvey>
> >         ";
> >       XmlDocument d = new XmlDocument();
> >       d.LoadXml(xml);
> >
> >
> >       ta.InsertQuery(
> >         11000,
> >         12731,
> >         new System.Xml.XmlNodeReader(d),  //XmlReader
> >         DateTime.Parse("10/19/2004 9:11:32 AM"));
> >
> >       ta.InsertQuery(
> >         11000,
> >         12731,
> >         xml,  //string
> >         DateTime.Parse("10/19/2004 9:11:32 AM"));
> >
> >
> > David
> >
> >
> >

"Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
news:187FDA40-2216-4D72-A74C-7EF10164C8DE@microsoft.com...
> Following on from the above posts I have also tried doing this the long
> way
>
> Dim con As New
> SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
>
>        Dim cmd As New SqlCommand
>        cmd.CommandType = CommandType.StoredProcedure
>        cmd.CommandText = "InsertFormData"
>        cmd.Connection = con
>
>        Dim parameter As SqlParameter = cmd.Parameters.Add("@FormID",
> SqlDbType.Int)
>        parameter.Value = Data.FormID
>
>        parameter = cmd.Parameters.Add("@UserID", SqlDbType.Int)
>        parameter.Value = Data.UserID
>
>        parameter = cmd.Parameters.Add("@CompletionDate",
> SqlDbType.DateTime)
>        parameter.Value = Data.CompletionDate
>
>        parameter = cmd.Parameters.Add("@FormData", SqlDbType.Xml)
>        parameter.Value = Data.FormData 'which is the xml document

"David Browne" wrote:

>
> "Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
> news:187FDA40-2216-4D72-A74C-7EF10164C8DE@microsoft.com...
> > Following on from the above posts I have also tried doing this the long
> > way
> >
> > Dim con As New
> > SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
> >
> >        Dim cmd As New SqlCommand
> >        cmd.CommandType = CommandType.StoredProcedure
> >        cmd.CommandText = "InsertFormData"
> >        cmd.Connection = con
> >
> >        Dim parameter As SqlParameter = cmd.Parameters.Add("@FormID",
> > SqlDbType.Int)
> >        parameter.Value = Data.FormID
> >
> >        parameter = cmd.Parameters.Add("@UserID", SqlDbType.Int)
> >        parameter.Value = Data.UserID
> >
> >        parameter = cmd.Parameters.Add("@CompletionDate",
> > SqlDbType.DateTime)
> >        parameter.Value = Data.CompletionDate
> >
> >        parameter = cmd.Parameters.Add("@FormData", SqlDbType.Xml)
> >        parameter.Value = Data.FormData 'which is the xml document
>
> The value can be passed as a string or as an XmlReader.  Not, strangely, as
> an XmlDocument.
>
> Try
>        parameter.Value = new System.Xml.XmlNodeReader(Data.Formdata)
>
> Assuming Data.Formdata is a System.Xml.XmlDocument.
>
> David
>
>
>

"David Browne" wrote:

>
> "Martyn Fewtrell" <mfewtrell@newsgroup.nospam> wrote in message
> news:28DAF17A-3D96-4804-A1B3-33DBC223F6F1@microsoft.com...
> > Thanks David
> >
> > That appears to have done the trick!
> >
> > Will there be similar issues reading the data back?
> >
>
> Reading back you will get a string.
>
> David
>
>
>