Passing a big query result to a procedure

Asked Apr 26 '18 at 07:19

Active Apr 26 '18 at 07:30

Viewed 517 times

I'm trying to serialize a table into XML by select .. for xml and then pass this result (always a single result) into my procedure (written in C# as DLL to a stored procedure).

To accomplish that I'm setting the result into a variable:

DECLARE @xml varchar(max)
select @xml=(select  * from table for xml)
execute db.dbo.procedure
  @xml
go

The problem is that I'm limited by the max of varchar, it's not big enough and my result might be as big as 50MB tops, what can I do instead? thanks.

edited Apr 26 '18 at 07:30

asked Apr 26 '18 at 07:19

Purma Zalame

change to `varchar(max)` ? – Squirrel Apr 26 '18 at 07:21
1

Change `varchar(max)` to `xml` data type. But `varchar(max)` not limited to only 8000 characters. – DatabaseCoder Apr 26 '18 at 07:26
Check out this- https://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable – DatabaseCoder Apr 26 '18 at 07:27
varchar(max) is not big enough, this is what i'm doing if you can see – Purma Zalame Apr 26 '18 at 07:28
If you have source code available for db.dbo.procedure, then you may parse xml and store data in a temp table. Then make use of temp table in db.dbo.procedure. – Sandeep Apr 26 '18 at 07:28
@BhatiaAshish Seems the `xml` type is good cause it can store up to 2GB of data. I'm using c# and I have the `SqlXml` type, do you know how to get the string out of it? `xml.Value` will do it? – Purma Zalame Apr 26 '18 at 07:44
Sorry, No idea about c#. – DatabaseCoder Apr 26 '18 at 07:45
`varchar(MAX)` isn't big enough? It can store up to 2GB of data as well. If `varchar(MAX)` is too small, that means you have over 2,147,483,648 characters. – Thom A Apr 26 '18 at 09:00

Passing a big query result to a procedure

0 Answers0