TEXT Datatype in XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TEXT Datatype in XML

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-03 : 14:13:36

Hi,
Am using a XML to insert or Update datas in Table. In that particular table a column is for Comma seperated values. It will contains large no of data.
So i have used VARCHAR(MAX) for that field. But now its showing some error since VARCHAR(MAX) supports 8000 chars only in Procedures.

MY QUERY:

INSERT INTO TABLEA
(
ID,
PRODUCT,
VALUES
)
SELECT
mem.data.value('ID[1]','INT') as ID,
mem.data.value('PRODUCT[1]','VARCHAR(100)') as PRODUCT,
mem.data.value('VALUES[1]','VARCHAR(MAX)') as VALUES
FROM @XML.nodes('/NewDataSet/Table1')mem(data);

In above XML nodes value is the column which doesnt support TEXT datatype. It says The data type 'TEXT' used in the VALUE method is invalid.
Please help me in this to fix the Issue. VALUE column can contain 'n' number of datas.

Regards,
Kalai

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-03 : 14:33:00

Could you please provide sample xml data please

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-03 : 14:43:24

[code]VALUES[/code] is a reserved keyword.
You can tell from the error message[code]Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'VALUES'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'VALUES'.[/code]
Put your column names in square brackets and you'll do just fine.
[code]INSERT dbo.TableA
(
ID,
Product,
[Values]
)
SELECT data.value('ID[1]', 'INT') AS ID,
data.value('PRODUCT[1]', 'VARCHAR(100)') AS Product,
data.value('VALUES[1]', 'VARCHAR(MAX)') AS [Values]
FROM @XML.nodes('/NewDataSet/Table1') AS mem(data)[/code]

N 56°04'39.26"
E 12°55'05.63"

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-04 : 02:24:56

Its not the error mentioned for Column VALUE. Its an XML error. mem.data.value doesnt supports TEXT field.

Regards,
Kalai

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-04 : 02:29:04

My Sample XML;

<NewDataSet>
<Table1>
<ID>1</ID>
<PRODUCT>SUB_BRAND1</PRODUCT>
<PRODUCTVALUES>prod1,prod2,prod3,prod4,prod5........prodn
</PRODUCTVALUES>
</Table1>
<Table1>
<ID>2</ID>
<PRODUCT>SUB_BRAND2</PRODUCT>
<PRODUCTVALUES>prod1,prod2,prod3,prod4,prod5........prodn
</PRODUCTVALUES>
</Table1>
</NewDataSet>

Here the PRODUCTVALUES is mentioned as VALUES in the XML procedure. But its not the issue. Using VARCHAR(MAX) its working fine. But its not wokring for TEXT field.
Please give the right solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-04 : 03:14:25

WHERE are you using the TEXT datatype? For the XML select? The Values column?
Why are you using TEXT datatype at all? It is deprecated...

N 56°04'39.26"
E 12°55'05.63"

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-04 : 03:23:45

Have you read my 1st post. TEXT datatype is used in PRODUCTVALUE column. I have used VARCHAR(MAX) but it doesnt supports more than 8000 chars. My column will exceed 8000 chars.

Regards,
Kalai

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-04 : 07:00:45

VARCHAR(MAX) does support more than 8000 characters!
However, there are some limitations in SQL Server regarding the built-in string functions.
Try this

INSERT	dbo.TableA
	(
		ID,
		Product,
		[Values]
	)
SELECT	data.value('ID[1]', 'INT') AS ID,
	data.value('PRODUCT[1]', 'VARCHAR(100)') AS Product,
	CAST(data.value('VALUES[1]', 'VARCHAR(MAX)') AS VARCHAR(MAX)) AS [Values]
FROM	@XML.nodes('/NewDataSet/Table1') AS mem(data)

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-04 : 07:01:56

Kalaiselvan, I suspect the problem you are facing is somewhere else. I copied your code and ran it, and it seems to run without any errors, parsing and storing strings longer than 8,000. This is my test code:

-- Test XML with productvalues 60000 long
DECLARE @XML XML = CAST('<NewDataSet>
<Table1>
<ID>1</ID>
<PRODUCT>SUB_BRAND1</PRODUCT>
<PRODUCTVALUES>' as varchar(max)) + 
REPLICATE(cast(',prod1' AS VARCHAR(MAX)),10000) +
CAST('</PRODUCTVALUES>
</Table1> 
<Table1>
<ID>2</ID>
<PRODUCT>SUB_BRAND2</PRODUCT>
<PRODUCTVALUES>prod1,prod2,prod3,prod4,prod5,prodn
</PRODUCTVALUES>
</Table1>
</NewDataSet>' AS VARCHAR(MAX));

-- how long is the xml when cast to string? (60,235)
SELECT LEN(CAST(@XML AS VARCHAR(MAX)));

-- what is the length of product values if I shred it? 
SELECT	data.value('ID[1]', 'INT') AS ID,
	data.value('PRODUCT[1]', 'VARCHAR(100)') AS Product,
	data.value('PRODUCTVALUES[1]', 'VARCHAR(MAX)') AS [Values],
	LEN(data.value('PRODUCTVALUES[1]', 'VARCHAR(MAX)') )  -- length of values colum is 60000 as expected.
FROM	@XML.nodes('/NewDataSet/Table1') AS mem(data);

-- can i insert it into a table?
CREATE TABLE #tmp (id INT, product VARCHAR(255), vals VARCHAR(MAX));

INSERT INTO #tmp
SELECT	data.value('ID[1]', 'INT') AS ID,
	data.value('PRODUCT[1]', 'VARCHAR(100)') AS Product,
	data.value('PRODUCTVALUES[1]', 'VARCHAR(MAX)') AS [Values]
FROM	@XML.nodes('/NewDataSet/Table1') AS mem(data);

-- what is the lenght of the inserted string?
SELECT LEN(vals) FROM #tmp; -- it is 60,000
DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources