FOR XML results in StoredProc

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
AdV
Posts: 4
Joined: Wed 06 Jan 2010 13:06

FOR XML results in StoredProc

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

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
Posts: 4
Joined: Wed 06 Jan 2010 13:06

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

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
Posts: 4
Joined: Wed 06 Jan 2010 13:06

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

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
Posts: 4
Joined: Wed 06 Jan 2010 13:06

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

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.

Post Reply