March 11, 2008 at 10:51 pm
Comments posted to this topic are about the item XML Workshop XV - Accessing FOR XML results with ADO.NET
.
March 12, 2008 at 3:43 am
Jacob,
An interesting article as usual. I am curious as to why you aren't using SQLXML4 and SQLXML managed classes to do this. (Is this being covered later in the series?)
On a minor point, you say 'For example, you might need to dispose() the database connection after reading the information'. Wouldn't you just put the code in a 'using' block?
Best wishes,
Phil Factor
March 12, 2008 at 4:13 am
Hi Phil,
Thank you for bringing this up. I had plans to cover SQLXML in the later sessions. On the .NET part, i was pretty sure that I could be missing a lot of basic stuff. This was the reason why I put the following at the conclusion:
"This sample code is created for the purpose of demonstrating the basic usage. The sample applications are tested with the sample data and found to be working correctly. However, please note that the code is not highly optimized. You might need slightly different code for a production level application. For example, you might need to dispose() the database connection after reading the information. I leave that to the .NET developer in you"
Thanks for the comments.
Jacob
.
March 12, 2008 at 1:05 pm
Jacob,
I like the series but have a question. I don't understand the purpose of the following code...
//Read the data from XMLReader and Load into
//the String Builder
StringBuilder xmlData = new StringBuilder();
while (r.Read())
{
xmlData.Append(r.ReadOuterXml());
}
Is there a need for the StringBuilder? If so, what purpose does it serve? Could I not cast it over to an xmlDocument without the intermediary step? Any elaboration you could shed on the consuming side will be appreciated.
Thanks,
Paul Hunter
--Paul Hunter
March 12, 2008 at 5:23 pm
I think the purpose of the stringbuilder was to just get the XML output to visually compare that you can indeed read it. In practice you wouldn't use the stringbuilder.
I don't yet have the luxury of coding everything in .NET but is there a case where it might be more efficient to return something like a order, order details, part details, etc tables as correctly nested XML rather than as separate recordsets that are then put into a dataset? I expect the answer to be "it depends on how you want to use the data" but if anyone has some concrete experience I'd be interested to know.
Personally I'm not a big fan of XML, not because I'm against it, but merely because if I don't need to use it (eg populating the dataset above rather than having an XMLDocument) and don't gain anything I don't feel like going to the extra effort 🙂
March 12, 2008 at 9:36 pm
Thanks Ian,
I thought that may have been the purpose of the StringBuilder. I've had several developers ask me how to consume xml output from the database. So, when I saw this example I thought it might be a necessary step. Could you expand further as to why Sebastian just add the ToString() method call directly to the xmlReader?
Thank,
--Paul Hunter
March 12, 2008 at 11:52 pm
Hi Paul,
Thank you for the feedback. The code was just for the purpose of demonstration only. My focus was on getting the FOR XML results to the .NET application into an xmlReader. Once the XMLReader is available, any .NET programmer can easily access the data using his/her favorite .NET methods. It could be loading the data to an XmlDomDocument or any other method that suites the specific requirement of the user.
thanks
Jacob
.
March 17, 2008 at 5:16 am
Hi Jacob,
glad you are writing these easy to use articles. Key point I've learned is that the FOR XML clause makes the stream work with ADO.NET's ExecuteXMLReader. One shortcoming I found the other day in trying to retrieve some XML stored in a Text column in SQL 2000 was the fact that the column was of type text. This does not work, according to MSDN:
i.ExecuteXmlReader only works with XML data.
ii.XML data is either : (a) XML text stored in a ntext or nvarchar field (b) an XML stream (i.e. a query with a FOR XML clause) or (c) SQL2K5 onwards XML data type.
Thought I'd mention that to try to prevent others wasting time like I did on that.
March 17, 2008 at 5:37 am
Agreed.
.
March 19, 2008 at 12:05 am
Hai,
Iam Ram. My friend asked me one question, like we have 1 lakh record in a table. how will u write a select statement to optimize it?
I told him that if we have primary key, an ordinary select stmt will be faster.
After seeing this post, Using For XML Auto will be faster than ordinary select stmt. Am i Correct?
March 19, 2008 at 1:45 am
I dont think FOR XML will give you better performance. FOR XML may not be used as a means to increase performance. It is usually used when an XML output need to be passed to the calling application.
If you have a slow running query, you should look at the execution plan to see what makes the query slow. The execution plan might tell what is causing the problem.
.
September 17, 2008 at 6:26 pm
Hi All,
this article helps a lot to get an idea of solving my problems regarding sql-server data. my query looks like this:
SELECT
Company.UnternehmenName,
Company.UnternehmenID,
Department.Bereichname,
Department.BereichID,
Process.ProzessName,
Process.ProzessID,
SubProcess.SubProzessName,
SubProcess.SubProzessID
FROM Company
LEFT JOIN Department
ON Company.UnternehmenID = Department.BereichUnternehmenID
left JOIN Process
ON Department.BereichID = Process.BereichID
left join SubProcess
on Process.ProzessID = SubProcess.ProzessID
for xml auto,elements,type, ROOT('Company_Structure')
Anyhow the call of cmd.ExecuteXmlReader() runs into error. (InvalidOperationException)
What do I miss??
Regards Jörn
September 17, 2008 at 10:21 pm
Does this query run on SSMS? It looks like there is something going wrong with the query and it is not returning an XML stream.
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply