For XML query returns garbage

vdev · Post by **vdev** » Thu 11 Aug 2011 09:53

Hi, I have a prnativeclient to sql server 2005.

I execute a simple for XML query.

First thing I noticed is that the field is returned as BLOB and not as an XML field. I dont really bother about it.

Second, I try to display the blob field in a memo but I get garbage.. Same if I save to stream etc...

AndreyZ · Post by **AndreyZ** » Thu 11 Aug 2011 11:53

Hello,

I cannot reproduce the problem. When you use the FOR XML statement, SDAC creates TMSXMLField fields. I used the following code:

Code: Select all

MSQuery1.SQL.Text := 'SELECT * FROM DEPT FOR XML AUTO, TYPE';
MSQuery1.Open;
Memo1.Text := MSQuery1.Fields[0].AsString;

, and there were no problems with XML data displaying. Please specify the exact SQL statement you encounter problems with.

vdev · Post by **vdev** » Wed 17 Aug 2011 13:49

Hi,

I have tried doing the following things..

1. Switch to prnativeclient, get the field as xml (ok) and then link a tprovider and a tclientdataset. When I open the tclientdataset i get an error that field 'myxmlfield' is of an unknown type.

2. If I dont use the 'TYPE' clause in the ending of the xml query, then the field is returned as a tblobfield. However when I try to save the field I get spaces after each letter.

What database do you use so I can create you a sample project?

Dimon · Post by **Dimon** » Thu 18 Aug 2011 08:27

1. The TMSXMLField field is the specific class for processing XML fields, created by SDAC. TClientDataset doesn't know such field data type and cannot process it. To solve the problem, you should use BLOB field instead of TMSXMLField.

2. If you don't use the XML field type, SQL Server returns XML data in its own format and you need to process it yourself. SDAC doesn't have any means for processing such data.