In addition to the great answer you have....

Try using /text() as part of the xpath when using ".value" ...

DECLARE @xml XML

SELECT @xml = CONVERT(XML, @xmlVarchar, 2)

SELECT @xml.value('(/VehicleInfoMessage/@Type/text())[1]', 'varchar(10)') AS 'Type'

,...

Oh, I don't...

PiMané (9/28/2012)

---

So my actual question is: for little XML since time is almost the same and sp_xml_removedocument doesn't take that much, should OPENXML be used or the memory is going...

PiMané (9/28/2012)

---

For small XML there's no difference. The 2nd Execution...

I tried this simple test - on SQL 2012 Xquery wins - interesting to see if 2008 is the same.

-- Prepare some bigger XML data

DECLARE @xml XML

DECLARE @detail VARCHAR(4000)...

You might get away with this, but to be truthful, I haven't checked whether it would always guarantee the correct order/matching.

The xml is not easily handled in T-SQL because of...

I wonder why you would want to split a postcode?

Add another column to your data set that has the correct ordering values

Have a lookup to another table that gave you that ordering.

SELECT ....

....

...

Just checking... are you actually on sql 2005?

Ok, so if that doesn't appeal, just set CONTEXT_INFO after you open the connection

DECLARE @ci VARBINARY(128)

SET @ci = 'MyUser'

SET CONTEXT_INFO @ci

Then in your trigger,

use CONVERT(VARCHAR(128),CONTEXT_INFO()) as the user name.

select

sum(1-void) as Countofsuccess,

sum(void) as countoffailure

from test

IF you are not using the Workstation ID / host_name for anything, that is quite handy...

Just set the Workstation ID in your connection string (a SqlConnectionStringBuilder helps nicely for this)

e.g.

Data...

Yep, I think that's flogged that one to death. I'm sleepy. G'night.

And the original 1M rows on my QUAD/4GB

Another 10M rows with MOD 50000 and a new code

SQL Server parse and compile time:

CPU time = 0 ms,...

Results for 10M rows with MOD 500000 on CustomerID on QUAD core desktop with 4GB ram.

========== Code From Article ===========================================

SQL Server Execution Times:

CPU time = 1576...