Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

XML Workshop XV - Accessing FOR XML results with ADO.NET Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2008 10:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item XML Workshop XV - Accessing FOR XML results with ADO.NET

.
Post #467897
Posted Wednesday, March 12, 2008 3:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 577, Visits: 2,503
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
Simple Talk
Post #467968
Posted Wednesday, March 12, 2008 4:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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


.
Post #467980
Posted Wednesday, March 12, 2008 1:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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
Post #468343
Posted Wednesday, March 12, 2008 5:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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 :)



Post #468435
Posted Wednesday, March 12, 2008 9:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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
Post #468485
Posted Wednesday, March 12, 2008 11:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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


.
Post #468508
Posted Monday, March 17, 2008 5:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 3:00 AM
Points: 18, Visits: 24
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.
Post #470157
Posted Monday, March 17, 2008 5:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Agreed.

.
Post #470161
Posted Wednesday, March 19, 2008 12:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 21, 2010 2:04 PM
Points: 1, Visits: 6
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?
Post #471380
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse