SQLXML: Output

[rzirpolo]

I am running this basic select statement to get the output in XML format..

SELECT client_code, description
FROM client
FOR XML auto

There should be 23 rows worth of data in the script but it seems to cut off after a specific amount of characters, hence not displaying all the data as a result of this select statement.

Is there a parameter or setting I need to amend in order to display the full line of XML code ?

 

[TonyKhela]

Are you using Query Analyser. If so, click on Tools. Options, Results and then change the "maximum characters per column" to 8192.

Not sure how to display more than 8192 charcters.
In fact I'd like to know how to do this, so if anyone can help, it'd be great.

TK

 

[rzirpolo]

Ok, thanks for that. But I need more than 8192 characters.

As you said if anybody knows that would really help me.

Thanks

 

[TonyKhela]

OK, now I've tried a couple of things:
1.
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(xmlspmap) from dbo.ImportConfig where id=1
READTEXT dbo.ImportConfig.XMLSPMap @ptrval 0 18334

this is still constrained by the Query Analyser's "maximum characters per column" value of 8192.

I also tried this:
declare @var1 varchar(8000)
declare @var2 varchar(8000)
declare @var3 varchar(8000)
declare @var4 varchar(8000)
declare @var5 varchar(8000)
declare @var6 varchar(8000)

select @var1=SUBSTRING(xmlspmap, 1,8000) from dbo.ImportConfig where id=1
select @var2=SUBSTRING(xmlspmap, 8001,16000) from dbo.ImportConfig where id=1
select @var3=SUBSTRING(xmlspmap, 16001,24000) from dbo.ImportConfig where id=1
select @var4=SUBSTRING(xmlspmap, 24001,32000) from dbo.ImportConfig where id=1
select @var5=SUBSTRING(xmlspmap, 32001,40000) from dbo.ImportConfig where id=1
select @var6=SUBSTRING(xmlspmap, 40001,48000) from dbo.ImportConfig where id=1

print @var1
print @var2
print @var3
print @var4
print @var5
print @var6

But again the substring function did not like the 'more than 8000 characters' thing.

so still looking or an answer..

Any ideas??