Error when Convert or Cast functions from Varchar to XML datatype

Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems.

When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below 

I am trying to do convert(xml,MyVarcharColumn)

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

Also i tried with casting and getting same problme. is there any way to convert


please suggest me




12/29/2007 3:42:37 PM 29906 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 37


From your description, it seems that you met trouble while you are going convert the field type from ntext to xml, right?

Based on my understanding , there are several factors which may cause the failure of the converting. Please make sure that you are not using “UTF-8” for your XML encoding in your ntext field. You can use “UTF-16” instead.

Another factor is you should make sure that the XML schema is right and the tags are in the right format, otherwise, it would also cause the failure of the converting.


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
1/2/2008 5:18:30 AM

Similar Artilces:

convert datatype to sql datatype
I have included the code that converts datatype to sql datatype. The columns belong to the dataset. However I want to know if there is a more direct method of dealing with this instead of multiple if then else or switch case statements.  If column.DataType.ToString = "System.Double" ThencolumnType.Add("bigint") ElseIf column.DataType.ToString = "System.String" ThencolumnType.Add("ntext") ElseIf column.DataType.ToString = "System.DateTime" ThencolumnType.Add("datetime") Else : columnType.Add(column.Dat...

"Syntax Error Converting varchar to int" in a column that is datatype=varchar
In my .NET application, I'm getting the following error: Syntax error converting the varchar value 'Me' to a column of data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'Foo' to a column of data type int.Source Error: Line 19647: System.ComponentModel.Design.HelpKeywordAttribute("

How to convert varchar to xml datatype in sql2005
Hi,  I have a talbe with a column type varchar(8000). i am facing problems sometimes as it corsses the limit also have the problems with special characters. so i went through few articles and been advised to use xml datatype. but when i am changing comumn name from varchar (8000) to xml as:  alter table tblStudentForm alter column FormDetails xml not null  i am getting following error: Msg 9400, Level 16, State 1, Line 1 XML parsing: line 46, character 402, unexpected end of input The statement has been terminated.   Please any one can advice how to alter on this....

Getting standard SQL datatypes from XML, i.e. XML->SQL
Hi all, I'm wanting to get standard SQL data types from an XML document in ASE. Looking at the documentation in 'XML Services in Adaptive Server Enterprise', it all seems to be concerned with transforming SQL into XML, or manipluating XML. e.g. how do you get a query like :- select filename, xmlextract("//book/title" , content) from xmlsfsTab to return a 'SQL' result set so the title column does NOT containt XML fragments, i.e. it looks like:- filename title ---------------- -------------------------------- bookstore.1.xml ...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

CAST function cause a SQL error. Is there any check to avoid this?
Hi all, Using ASA6.03 and ASA9.02 I have a computed column in a report like this: SELECT CAST(text_col1 AS INT) FROM table1 When text_col1 contains apha numeric data the report issues a SQL error. Is there any way to check if the value of text_col1 can be converted to INT like: SELECT IF ckeckisnumber(text_col1) THEN CAST(text_col1 AS INT) ELSE 0 ENDIF FROM table1 Thanks Stelios Stylianou "Stelios Stylianou" <> wrote: > Using ASA6.03 and ASA9.02 > > I have a computed column in a report like this:...

Error occuring wen trying to write to xml file from sql datasource
Hi all I am having a spot of difficulty with the following code, it gives me a InvalidOperation unhandled by user code error: Imports System.Data.Sql Imports System.Data.SqlClient Imports System.Xml.XmlWriter Imports System.XmlPartial Public Class RSStest Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)Dim cm As New SqlCommand ' set the conenction and query string cm.Connection = cn c...

Using CONVERT function to convert NUMERIC to VARCHAR
I am creating a trigger on a table where 2 columns are defined as numeric. This trigger captures the data and inserts it into another table where I want to combine the numeric data with a statement. I've tried using the CONVERT function, but it is giving me a mis-matched token error. I've looked through the documentation online, but it is not really helping me or I'm not understanding correctly. I've pasted a snippet of what I'm trying to do without using the CONVERT function. **user_level and region_id are the numeric values** select @whatchanged = case ...

PB 11.2/MS SQL 2005 Error converting data type varchar to numeric
Am getting the above error from PB 11.2 when executing a stored proc. The proc take no parameters. Once i get this error, i cannot execute the proc succesfully from PB 11.2 until i recompile the stored proc. At the same time the proc works fine from Query Analyzer, as well as from PB 7. What is PB 11.2 doing to mess this up? Any's wrecking havoc on our production app. Forgot to add the error is C0195:SQLSTATE =3D 22018 On Dec 13, 4:00=A0pm, wrote: > Am getting the above error from PB 11.2 when executing a stored proc. > The proc take no...

error in SQL Store Proc ''Conversion failed when converting the varchar value to data type int'
Hi , I am having problem and error of 'Conversion failed when converting the varchar value to data type int ' CREATE PROC dbo.testingloop (DECLARE @testid varchar(max))ASBEGINSET NOCOUNT ONDECLARE @SQL varchar(600)SET @testvalue = CURSOR FORSELECT [test ID])  FROM Data42 where [test ID] = 36OPEN @testvalue      FETCH NEXT     FROM @testvalue INTO @testid      WHILE @@FETCH_STATUS = 0       BEGIN      ' My update statement here  ...

Converting XSD to GridView Formate AND GridView to Xml convertion 2005
hi...           I am new to .net 2005.My problem is        1.I have XSD . in that i am having Entair fields in that               2.i want to convert into Table form.        3.then i want to insert values in that table.        4.Finally i want to convert values in XML formate that's it.. it is very easy in .net 2003.note:-i dont want to use any code.every thing can do with in built ...

used a to converter and am now getting a syntax error.
ok I used a cool utility at to convert some to and it spit out a bunch of code which almost works but I am getting a syntax error on this line Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) I tried it that way and also like Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) but either way gave the same error Compiler Error Message: BC30035: Syntax error. Source Error: Line 26: Line ...

hi, im a query where i use the convert function (convert(field, varchar(15))), when i run this i get an error, Query:Field name missing. Am i using the convert function wrongly ?, this query is beign run against a Sybase server. Thanks. Hector Coss wrote: > hi, im a query where i use the convert function (convert(field, > varchar(15))), when i run this i get an error, Query:Field name > missing. Am i using the convert function wrongly ?, this query is > beign run against a Sybase server. > > Thanks. You should give alias to the result. Something like conver...

Converting SQL to XML
I'm trying to convert some sql data to xml with some visual basic code-behind... but it's not working. When I fire off the page it just flashes and stays blank.  It doesn't display at all my label I tell it to when the code is finished. Right now I'm firing it off whent he page loads just to simplify things, but I previously had a button that was used to initialize it.  It failed with the same result, page flashes, no label displays.  I even check up on my server where the xml doc is supposed to be saved to but it's not found.   Here's my code behind: Imports System....

Web resources about - Error when Convert or Cast functions from Varchar to XML datatype -

Wave function - Wikipedia, the free encyclopedia
... (a ball attached to a spring ) in classical mechanics (A-B) and quantum mechanics (C-H). In quantum mechanics (C-H), the ball has a wave function ...

TV binging, exercise skipping linked to poor cognitive function
... their fit, less TV-addicted counterparts. In particular, couch potatoes had slightly lower brain processing speeds and worse executive function, ...

iPhone best tricks and functions - Business Insider
Your iPhone can do a lot more than you think.

Facebook Updates Search Function; Now Is A Great Time To Run And Check All Your Privacy Settings
Facebook search is… well, kind of a joke. It can tell you which 400 people in your area have similar names to that one person you want to connect ...

Review: HP Spectre 360 A Perfect Mix Of Style, Form, Function
HP Inc. delivers a brushed-aluminum laptop with a new 6th Generation Intel i7 processor, making it one of the best-looking, best-priced and best-performing ...

Why Global Corporations Need To Redesign Their Strategic Planning Function
Corporate strategy teams play an important role in guiding business leaders to make smarter, better and more informed decisions, especially in ...

Sponsored post: Garmin’s fenix 3 Pairs Form and Function with GPS Watch
... timepiece to polish off a professional look, or strap on a rugged stopwatch for our workouts. But when it comes to everyday use, the functional ...

Code Craft – Embedding C++: Timing Virtual Functions
... compiler just like the standard language types. A subtype inherits the characteristics of its parent type and can use, or not, the member functions ...

Madras HC asks police to allow Tipu’s birth anniversary function
... Station on a petition filed by Ismail, General Secretary of Thamizhaga Makkal Jananayaga Katchi, seeking grant of permission to hold the function. ...

Colorado governor: This Planned Parenthood shooting may be a function of inflammatory rhetoric from bloggers ...
Via Grabien , skip to 4:45 of the clip below for the key bit. I naively thought we were (mostly) done with “climate of hate” stupidity after ...

Resources last updated: 12/4/2015 8:35:37 AM