Is there any specific reason for still using OPENXML over XQuery?

How about comparing the performance against the following XQuery statement?

SELECT

v.value('id[1]','NVARCHAR(256)') AS ID,

x.value('@href[1]','NVARCHAR(256)') AS [Link],

x.value('@rel[1]','NVARCHAR(256)') AS [LinkRel],

z.value('Status[1]','NVARCHAR(256)') AS [Status]

FROM @document.nodes('entry[1]') T(c)

CROSS...

So let's asume the given scenario (no set based solution possible and a unchangeable sproc).

Do you have any proof of concept available that a while loop would perform better than...

Ok, you did the first stepby providing the DDL script for the table.

The second step would be to provide some ready to use (fake) sample data (provided as INSERT statements)....

Robert Frasca (8/20/2010)

---

Forget it. I knew as soon as I asked on this forum I'd see the answer.

The second column is using the value set for the first column...

@supachoi:

Replacing a c.u.r.s.o.r with a while loop due to performance reasons is like suggesting to cure cancer with AIDS.

Both solutions (loop and cursor) are row based (aka RBAR) won't give...

Ok, here's an example on how to do it. Howver, I strongly recommend you get the vendor involved providing you a function with the same functionality (iTVF preferred...).

This would not...

thisisdeepthy (8/20/2010)

---

cAN YOU PLEASE ALSO ADD THE SCRIPT WITH THIS TEXT VALIDATION IN THE CURSOR ? AND ALSO SLIGHT CHANGES IN THE REQ, NEED TO MOVE THE VALID FILES TO...

Nice one!

First thing: there is no such thing like the 5th row.

The order of rows in a table doesn't really matter. Not even the physical order of rows really is deterministc...

There is definitely some room for improvement.

To test the sample code I would like to have table def (DDL script) including index scipt(s) for the tables involved and some ready...

Either use @@ROWCOUNT as the immediately following comand after your insert statement or if you'd like to get more details from the values inserted use the OUTPUT clause in your...

Having a closer look at your document it seems like there is an inconsistent default namespace declaration:

< entry xmlns="http://www.w3.org/2005/Atom" >

< Contact xmlns="http://ws.constantcontact.com/ns/1.0/" >

Either you set those two elements to the...

If the ClientId column isn't indexed you will end up with a table scan causing bad performance. If you're allowed to, add the ClientId (nonclustered) index and see if it...

What is your expected output based on the sample data provided?

Since you have a named xml doc you need to use the namespace declaration in your query.

I don't know if...

You're welcome!

That's exactly the attitude that's needed: get an idea/code snippet, do some research, learn how it works and modify it to your needs. Excellent job on your side!!!