Posted by AJA on 03/22/07 07:26

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

[Back to original message]