Get XML node as 'text' data type
-
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) ENDThe 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? Jim Conigliaro jconigliaro@ieee.org -
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) ENDThe 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? Jim Conigliaro jconigliaro@ieee.orgTry
varchar(MAX)instead oftext.