locked
Get XML node as 'text' data type RRS feed

  • Question

  • We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

    CREATE PROCEDURE as MyProcedure @myData xml
    BEGIN
    INSERT INTO MyTable (FirstName, LastName, Notes)
    SELECT
    MyNode.value('FirstName[1]','varchar(50)'),
    MyNode.value('LastName[1]','varchar(100)'),
    MyNode.value('Notes[1]','text')
    FROM @myData.Notes('Person') as R(MyNode)
    END


    The problem is with the notes field. The cast to the data type text fails with the following error:
    The data type 'text' used in the VALUE method is invalid.

    The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

    Any ideas?
    Thursday, May 25, 2006 9:48 PM

Answers

  •  

    Try using 'varchar(max)' instead of 'text'

    Friday, May 26, 2006 6:55 AM

All replies

  •  

    Try using 'varchar(max)' instead of 'text'

    Friday, May 26, 2006 6:55 AM
  • Perfect.  Thanks!
    Friday, May 26, 2006 1:59 PM
  • What would be a datatype for the value for an image?  Will varchar(max) work for it as well?
    Wednesday, July 18, 2007 8:40 PM
  • In Sql Server 2005, we have xml datatype. But i felt with the same error "The data type 'xml' used in the VALUE method is invalid".

    If i use varchar(max), it parses the xml and table has only element/attribute values not xml format.

    I want XML format inserted into the table.

    Any idea Please?

    Thanks In Advance.

    Tuesday, January 4, 2011 10:58 AM
  • Use the .query method instead of the .value method to return XML.  See here:

    http://msdn.microsoft.com/en-us/library/ms190798.aspx

    Please mark the post as answered if you feel it's helped.

    • Proposed as answer by wBobEditor Tuesday, January 4, 2011 12:17 PM
    Tuesday, January 4, 2011 12:15 PM
    Answerer