SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 2523
Comments posted to this topic are about the item XML Workshop XV - Accessing FOR XML results with ADO.NET

.
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4780 Visits: 3031
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
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 2523
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

.
SQLNightOwl
SQLNightOwl
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 523
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
Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4524 Visits: 445
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 Smile



SQLNightOwl
SQLNightOwl
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 523
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
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 2523
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

.
chris lewis-447023
chris lewis-447023
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 2523
Agreed.

.
m.ramramram
m.ramramram
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search