FOR XML results in StoredProc

AdV · Post by **AdV** » Wed 06 Jan 2010 13:14

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.

Dimon · Post by **Dimon** » Wed 06 Jan 2010 13:46

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

AdV · Post by **AdV** » Thu 07 Jan 2010 00:01

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.

Dimon · Post by **Dimon** » Fri 08 Jan 2010 08:55

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

AdV · Post by **AdV** » Tue 12 Jan 2010 13:40

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,

Dimon · Post by **Dimon** » Wed 13 Jan 2010 07:20

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.

AdV · Post by **AdV** » Wed 13 Jan 2010 11:25

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?

Dimon · Post by **Dimon** » Thu 14 Jan 2010 06:59

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.

Dimon · Post by **Dimon** » Fri 15 Jan 2010 08:51

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.