Brian Filppu
3/27/2007 11:23:00 PM
Aja,
max varchar doesn't exist in sql 2000.... You can't create a local variable
larger than varchar(8000). You need to create a stored procedure that takes
in a text or ntext variable and call the stored proc. I'm not sure how you
are calling the sql, for example, it's easy to call a stored proc from .net
and pass in the xml document as text - this works well in a soa where you a
dealing with .net webservices that have to save/parse the xml in sql server.
"AJA" wrote:
> Hello
> I watchet some posts but did not find answer to my question : how to
> import data from xml into mssql 2000 using t-sql?
>
> i tried:
>
> USE Northwind
> if(object_id('dbo.test_xml') is not null)
> drop table dbo.test_xml
> go
> create table dbo.test_xml (Id int identity(1,1), col1 text)
> go
> insert into test_xml(col1) values('')
> go
> declare @cmd varchar(512)
> set @cmd = 'D:\Progra~1\Micros~3\MSSQL\Binn\TextCopy.exe /S /U
> [user] /P [password] /D Northwind /F c:\pobierz.xml /T
> test_xml /C col1 /I /W "where Id = 1"'
> exec master..xp_cmdshell @cmd, 'no_output'
> go
> declare
> @hdoc int
> , @doc varchar(1000)
> select @doc = col1 from dbo.test_xml
> exec sp_xml_preparedocument @hdoc out, @doc
> select @hdoc as hdoc
> -- ...
> select * from openxml(@hdoc, ...
>
> --...
> exec sp_xml_removedocument @hdoc
>
> But my documet have more than 1000characters and more than max varchar. So
> how to do that?
>
>
> Best regards
> AJA
>
>