Table Adapter and Xml DataType

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Martyn Fewtrell said:
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.
Click to expand...

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
 
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 said:
Martyn Fewtrell said:
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.
Click to expand...

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
Click to expand...
 
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

conVBO.Open()
cmd.ExecuteNonQuery()
conVBO.Close()

This appears to generate the same exception as the Table Adapter. The
relevant bit being:

Object must implement IConvertible.

InvalidCastException: Failed to convert parameter value from a XmlDocument
to a String

So I'm not quite sure how you are supposed to get your Xml cocuments into an
SQL 2005 database.

--
Regards

Martyn Fewtrell


Martyn Fewtrell said:
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 said:
Martyn Fewtrell said:
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.
Click to expand...

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
Click to expand...
Click to expand...
 
Martyn Fewtrell said:
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
Click to expand...

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
 
Thanks David

That appears to have done the trick!

Will there be similar issues reading the data back?
 
Martyn Fewtrell said:
Thanks David

That appears to have done the trick!

Will there be similar issues reading the data back?
Click to expand...

Reading back you will get a string.

David
 
Thanks David for all your assistance.

I now have a successful Method to Read and Write so hopefully there will be
no looking back from here on!
 
Back
Top