Using Fields[0].Value to get XML from FOR XML RAW, ELEMENTS query is messed up

Question

I have a query that uses FOR XML RAW, ELEMENTS to return a SELECT query as a structured XML document. However, when I get the result using a TSQLDataSet by using Fields[0].Value, the result is different from what I see when I run the query in SQL Server Management Studio.

What I see in the result from the TSQLDataSet:

੄customerIdфname၄governmentNumberไdebtorAddress1ไdebtorAddress2ไdebtorAddress3ไdebtorAddress4ࡄpostCodeୄcontactNameՄphonë́faxൄcustomerSinceՄtermsلactiveไcurrentBalanceلDebtorခŁ䄁ഃӤ

What I see in the result in SSMS:

<Debtor>
  <customerId>C0E449E5B2C      </customerId>
  <name>New Customer 2                               </name>
  <governmentNumber>                </governmentNumber>
  <debtorAddress1>Address Line 1                                    </debtorAddress1>
  <debtorAddress4>Address Line 4                  </debtorAddress4>
  <postCode>1234                </postCode>
  <phone>1234567890               </phone>
  <fax>1234567890               </fax>
  <customerSince>2013-06-10T18:16:06.213</customerSince>
  <terms>M   </terms>
  <active>true</active>
  <currentBalance>0.0000</currentBalance>
</Debtor>

Is there a particular way it should be executed to get the right result?

Answer 1

AFAIK this is a DbExpress limitation. I know how overcome this, but using ADO (the returned data must be requested using a special parametrized object and a set of ADO streams). However you can use a workaround converting the XML data to a string in the server side sorrounding the sentence with a select (subquery) or just using a simple CAST statement.

For example if you sentence is like so

  SELECT Foo, Bar FROM FooTable FOR XML RAW, ELEMENTS   

you can rewrite to

 SELECT (SELECT Foo, Bar FROM FooTable FOR XML RAW, ELEMENTS)   

or you can rewrite to (use a CAST VARCHAR or NVARCHAR)

SELECT CAST( (SELECT Foo, Bar FROM FooTable FOR XML RAW, ELEMENTS) AS VARCHAR(MAX))

and finally

Retrieve the result like this

 SQLDataSet1.Fields[0].AsString