XML Workshop XV - Accessing FOR XML results with ADO.NET

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » XML Workshop XV - Accessing FOR XML results...

13 posts, Page 1 of 2

1 2 »»

XML Workshop XV - Accessing FOR XML results with ADO.NET

Rate Topic

Display Mode

Topic Options

Author

Message

jacob sebastian

Posted Tuesday, March 11, 2008 10:51 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

Comments posted to this topic are about the item XML Workshop XV - Accessing FOR XML results with ADO.NET

.

Post #467897

Phil Factor

Posted Wednesday, March 12, 2008 3:43 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

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

jacob sebastian

Posted Wednesday, March 12, 2008 4:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

SQLNightOwl

Posted Wednesday, March 12, 2008 1:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:55 PM
Points: 179, Visits: 391

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

Ian Yates

Posted Wednesday, March 12, 2008 5:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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

SQLNightOwl

Posted Wednesday, March 12, 2008 9:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:55 PM
Points: 179, Visits: 391

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

jacob sebastian

Posted Wednesday, March 12, 2008 11:51 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

chris lewis-447023

Posted Monday, March 17, 2008 5:16 AM

Grasshopper

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

jacob sebastian

Posted Monday, March 17, 2008 5:37 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

Agreed.

.

Post #470161

m.ramramram

Posted Wednesday, March 19, 2008 12:05 AM

Forum 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 »

13 posts, Page 1 of 2

1 2 »»

Permissions