Read XML > 8k in sql TEXT column?

Hi,
I have a SQL2k database that holds XML in a TEXT column because it is
greater than 8k, I need to extract the value from a couple of fields in the
XML. How can I do this? Examples would be great; I'm new to world of XML.
BTW I'm don't have control of the database design so I can't change the
structure of the db to hold the data in a more senisble way.
Thanks,
Paul.Hello Paul,
Thank you for posting.
Regarding on the read XML data from multiple columns in SQLServer database,
are you using ADO.NET components to access the database table? Based on my
understanding, if we can make sure the content order of those columns in
the database table, we can just use ADO.NET datareader or dataadapter to
query the records out, and then combine the text in those columns together
to construct a complete text stream.
Please let me know if you have any detailed question or concerns here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Steven,
Thanks for the reply, I'm not using ADO.NET.
All the data is in a single SQL2k Text column but it is greater than 8k in
size. What I'm looking to do get data out of a couple of elements and write
them back to two sql columns using a stored procedure.
The basic issue is because I can't delclare a variable of type text in the
procedure. How can I use the sp_xml_preparedocument and OPENXML commands in
a
procedure with a large text column with more than 8k of XML.
Thanks,
Paul.
"Steven Cheng[MSFT]" wrote:

> Hello Paul,
> Thank you for posting.
> Regarding on the read XML data from multiple columns in SQLServer database
,
> are you using ADO.NET components to access the database table? Based on my
> understanding, if we can make sure the content order of those columns in
> the database table, we can just use ADO.NET datareader or dataadapter to
> query the records out, and then combine the text in those columns together
> to construct a complete text stream.
> Please let me know if you have any detailed question or concerns here.
> Regards,
>
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Thanks for your response Paul,
So you're going to done the multiple text column string concatenate at
server-side through T-SQL. Based on my research, I'm afraid this is not
supported in SQL 2000 since the datatype are limited to varchar or ntext
which has 8000 limitation. And we can not have local variable that have
larger value return from concatenate of such columns. So we may consider
done it at upstream level(in data access component, ADO or ADO.NET).
BTW, if it is possible to upgrate to SQL 2005, there has built-in sql xml
type and CLR code supported which may help resolve such issue.
Sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.