Use of cursors with XML data in sql 2000

Question

Post reply

Use of cursors with XML data in sql 2000

Aur_Ek_SQL

Old Hand

Points: 302
More actions
September 10, 2009 at 6:01 am

#103820

I have SP which takes input as XML in sql 2000 and then I open a cursor using that XML for specific node level. Then I open the cursor using XML and update the table records one-by-one.

2nd way is I declared the @table and inserted the XML node level records into @table variable and then doing update joining the @table with main table which I need to update.

after some monts I need to migrate this db from 2000 to 2008.

Please suggest the correct way to Use of cursors with XML data in sql 2000.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you please provide some sample data to test against together with your expected result?

For details on how to post those data please see the link in my signature.

It's some kind of a challenge to design a xml procedure in SS2K knowing that the server will be moved to 2K8 in the near future:

Design for SS2K would limit you to OPENXML, leaving out the power of XQuery provided with SS2K5 and higher.

Design it for 2K8 right away using XQuery won't let you use it in SS2K.

If you have something working already for SS2K I'd recommend you leave it as it is and do a complete redesign for SS2K8. Most probably the cursor will be gone ...

If you need help, please describe what you need to do a little more detailed.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Aur_Ek_SQL Old Hand Points: 302 More actions · Answer 2

Above question if resolved.I need more info on whare clause for OPENXML. Can I use OPENXML like this :

select State,Effectivedate FROM OPENXML(@XmlTable, '/TemplateDto', 2)

WITH

(State char(2),EffectiveDate datetime) where State='CA'

I want to filterout the records while featching the records from XML?

Pls suggest..

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

Aur_Ek_SQL (10/9/2009)

Above question if resolved. ...

Would you mind sharing your solution so others can benefit?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Aur_Ek_SQL Old Hand Points: 302 More actions · Answer 4

Aur_Ek_SQL

Old Hand

Points: 302

October 9, 2009 at 10:06 am

#1064214

2nd way is I declared the @table and inserted the XML node level records into @table variable and then doing update joining the @table with main table which I need to update.