pass larg xml file to sql procedure

wilk_u

Hi,
i need to pass large xml file to sp in sql, tried:
- cant send xml file as parameter with adoConnection.parameters - no xml type in ado data types
- cant put command like EXEC function(XMLDoc.xml) cause nav crashes saying that text length exceeds string buffer size, also execute command form ado connection doesnt accept BigText
- tried to pass array of 1024 length text strings as ado parameter but then nav says array must be the same dimension, no matter if i set dimension to 10 [10x1024] or 1024 [1024x1024]
- tried to set adoCommand.CommandText(EXEC proc XmlDoc.xml) but also got an errror about string buffer size overflow

Did you guys find a solution to the similar problem? Any help will be very useful.

Regards

MarkHamblin

This may not be the most elegant solution, but it's the first one that popped into my head: save the XML to a blob field in SQL before calling the proc, then just pass the record ID of the blob to the stored proc. The proc can then just read the XML from the database instead of receiving it as a parameter.

- Mark

wilk_u

Thanks for answer, trying this i encounter a problem, sql throws an obvious error, cannot convert blob to xml which is a column in the table. Is there any way to convert it via sql?

Regards

MarkHamblin

You should be able to use CONVERT in SQL to switch to/from XML, though you shouldn't need to (should be an implicit conversion). If you created the blob field as an "image" field, you won't be able able to convert to XML.

wilk_u

Hello,
you mean changing field type in nav from blob to binary? Then it's only 250 chars..

Can you please explain this in more detail? Thank you

MarkHamblin

Yes, change the field type. Binary and varbinary can be 8K, or you could use a text field - basically the text equivalent of an image field (2GB storage).

If you're using a newer/current version of SQL, use varchar(max) instead of text - it also has 2GB storage.

wilk_u

Part which is a problem for me, what field type do you mean to choose in navision to be able to convert it to xml?

Procedure gets the record from navision table which has i.e field MyXml and converts that field to xml and does some stuff with it.
Point is, MyXml field can't be a blob, i can fit whole xml in it but can't convert it to xml in sql procedure cause blob is an image type in sql
Binary field in Navision is only 250 chars so it's to small.

What type of field in navsion do you mean?

Regards

Sog

are you sure the compression is not enabled on the blob field you are trying to use?

wilk_u

Compression property and casting directly from binary to xml made the job. Thanks guys!

wilk_u

I managed to convert file and put it in sql table, but when running this procedure from navision - nav freezes on sqlCommand.execute

SQLCommand := STRSUBSTNO('EXEC getXmlFile %1',
                          LastNo);

ADOConnection.Execute(SQLCommand);

LastNo is ans int value, xml no.

Procedure:

ALTER procedure [dbo].[getXmlFile] (@no int)
AS
declare @myxmlasblob varbinary(max)
declare @myxml xml
set @myxmlasblob = (select [xml] FROM NAV.dbo.[xmlTable] where [No] = @no)
set @myxml = (select CONVERT(xml,@myxmlasblob))
insert into tableToInsert (XmlFieldInThatTable) select @myxml

through sql mgmt studio on the same user and with the same argument everything works perfectly.

Any hints?

Thank you guys

wilk_u

Any hints on that?