Devart Forums

Using the MSStoredProc component I find that if I call a stored procedure which returns a FOR XML AUTO result, the xml type returned is in native binary format (not MS-BINXML) and I can see the blob data (MSStoredProc.fields.fields[0]) containing the xml type. What I really need is (obviously) just the xml as a (wide)string. I cannot find any examples or anything showing how to do this. Anyone have any suggestions or examples or know what the binary format is?

Thanks, Al.

SDAC creates the TMSXMLField field for the MSXML server type. To get XML as string you can use the TMSXMLField.XML property.

Thanks Simon, but this must be a bit different in the Delphi 7 version I'm using (yes v. old).
What are the ancestors for TMSXMLField, no TWideMemoField!

Thanks, Al.

In Delphi7 TMSXMLField is inherited from the TMemoField class because Deplhi 7 does not support Widestring blobs like TWideMemoField.

Simon,
still no joy. the fields.fields[0] that I'm getting back from msstoredproc insists its just a tblobfield. I'm unable to cast/convert it to a tmsxmlfield.
can you give me an example of its correct usage please?

Many thanks,

Please make sure that you have SQL Native Client installed on your client PC. To solve the problem you should use this provider because Standard OLEDB provider doesn't support XML fields. For this set the TMSConnection.Options.Provider property to the prNativeClient value.

Thanks Dimon, but makes no difference. I'm clearly not seeing the wood for the trees. this is my simplified code;

with FMSStoredProc do begin
StoredProcName := 'dbo.fnGetButton';
Prepare;
Params.ParambyName('but_id').AsInteger := 1; //name
Execute;

//fmsstoredproc.fields.fields[0] is now a Tblobfield containing native xml
//but cannot get it into a tmsxmlfield.

tried streaming out the blobfield into a tmsxmlfield but get exception. in fact, if I create a tmsxmlfield object and then try to savetofile the empty object I also get an exception, not what I expect.

Any tips?

I could not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script for creating database objects.

Also supply me the following information:
- the exact version of SDAC. You can see it in the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL server and client. You can see it in the Info sheet of TMSConnection Editor.

Thank you for the infomation.
Actually, for this query SQL Server returns a field of the blob type and we can't influence this. Therefore you can use the TBlobField.AsString property or the TBlobField.SaveToFile method to get the resulting XML.