October 5, 2007 at 3:12 pm
I have a CLR stored procedure that runs a complex query that returns a hierarchical XML document. The XML is built using nested sub-queries, each using FOR XML PATH(...) clauses. When I run the stored procedure natively from the SQL 2005 management console, I get a single row with a single field, but the field is a hyperlink. Clicking the hyperlink loads the entire XML string into a new window in the Mgt Console. It looks great, and contains a lot of text, because it's returning a lot of data. The "query analyzer" reports that 1 row was returned, even though there were many sub-queries returning many rows during the entire query process.
The trouble comes when I try to run the CLR stored procedure from a web service. I have tried the following methods: ExecuteDataSet, ExecuteReader, ExecuteScalar, and ExecuteXMLReader, and each one returns a block of XML that is truncated around 4100 characters. Any ideas on what I could do that will return the entire XML string to the web service?
Thanks
Doug
October 10, 2007 at 7:43 am
Hi Doug,
While it may not solve your problem, as you say you have tried ExecuteXmlReader, one thing that is worth noting is that SqlDataReader generally chops up large XML results. The workaround for this is to use ExecuteXmlReader. An article that has some more info on this is at: http://support.microsoft.com/default.aspx?scid=kb;en-us;q310378&id=kb;en-us;q310378&ln=en-us&rnk=1&sd=msdn&fr=0&qry=q310378&src=dhcs_mspss_msdn_srch&spr=msall
Regards,
Andras
October 10, 2007 at 8:46 am
Thanks. Haven't tried ExecuteXMLReader. I'll give it a shot. I did discover what you referred to - the XML is chopped up and placed into subsequent rows, so there are many rows in the resulting dataset. I can loop through and reconstruct the XML, and it seems to work ok.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply