retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql

retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>

Retrieving ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data