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 2000 Forums
 Transact-SQL (2000)
 storing SELECT ... FOR XML EXPLICIT rs

Author  Topic 

mattuas
Starting Member

6 Posts

Posted - 2002-07-31 : 20:07:49
SQLServer2000, I am using "select ..... FOR XML EXPLICIT" (which means I am controlling the format of my XML result). I need to store the returned XML - how can I do this? I cant use the FOR XML in an insert statement (therefore cant use temp table), I cant use cursors either. Help ... anyone ? ...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-31 : 20:20:21
This should help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336

You can export the XML into a file and then import the file into a table.

Go to Top of Page

mattuas
Starting Member

6 Posts

Posted - 2002-08-01 : 02:05:01
thankyou robvolk, one further question...
how do I call a bcp statement from my stored procedure?

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-01 : 05:40:55
use xp_cmdshell. Check out the syntax in BOL

e.g.
declare @cmdxmlout varchar(1000)
declare @ret int
declare @tempfullpath varchar(500)

set @tempfullpath = 'c:\test.xml'

set @cmdxmlout='bcp "' + @sql + '" queryout '+ @tempfullpath +' -S(local) -T -c -r -t'
exec @ret=master..xp_cmdshell @cmdxmlout,no_output



You may encounter problems getting the data back in however if you try and use BULK INSERT. It has a
problem with files whose size is divisible by 4 if there are no field/line terminators (as there would be in an XML file) - bcp in works fine with a format file and you can use a generic one for all files.
Here's an example of BULK INSERT going wrong - it drove me mad trying to figure it out during testing of exactly what you are trying to do

FILE CONTENT
1.txt 1
2.txt 12
3.txt 123
4.txt 1234
5.txt 12345


use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[testload]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [testload]
go
create table testload(filedata text NULL)
go

BULK INSERT testload FROM 'c:\load\1.txt'
BULK INSERT testload FROM 'c:\load\2.txt'
BULK INSERT testload FROM 'c:\load\3.txt'
BULK INSERT testload FROM 'c:\load\4.txt' --divisible by 4 !!
BULK INSERT testload FROM 'c:\load\5.txt'
go

select * from testload
go

if exists (select * from dbo.sysobjects where id = object_id(N'[testload]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [testload]
go

Results :

1
12
123
12345



I got so fed up with all this file stuff that I now just call a DTS package that
uses ActiveX ScriptTask and XMLDOM to do the work
If you want any examples let me know. Getting XML out of SQL and then back in again in the same procedure is a pain.

HTH
Jasper Smith
Go to Top of Page

mattuas
Starting Member

6 Posts

Posted - 2002-08-01 : 20:57:21
thanks so much for your help
cheers ... matt

Go to Top of Page
   

- Advertisement -