Yes, I'm here, waiting for some information from your side what you've found in the articles I pointed you at.

Did you actually check the articles I mentioned?

Maybe the people who...

Obviously, the problem cannot be reproduced easily... 🙁

Let's try it another way: Would you please run the following code on your system (after reviewing what it does, of course):

SELECT ...

Hi,

I'd recommend to read through Jacob Sebastians articles "XML Workshop ..." (search for those keywords in this site).

It covers a lot of subject on how to deal with xml data.

I...

Hi,

could you please provide table structure and sample data the same way you just did but for table wce_ilr and wce_contact together with some sample data that show the effect...

I strongly recommend to stay with a relational data model, meaning to have three columns: EMPCODE, Date, value.

Therewith you don't need any code to add a new column for a...

The OP mentioned it in the first post:

JohnG (8/19/2009)

---

I know that that...

It looks like it's an issue with your OR condition in your left outer join.

Check if your suspicious results have rows in wce_contact with the same company name, but none...

How about the following formula?

DECLARE @bitPos TINYINT

SET @bitPos = 4 -- EDIT: was 3

SELECT @i - ( ( FLOOR(@i/(POWER(2,(@bitPos-1)))) % 2 - @b) * (POWER(2,(@bitPos-1))))

Some explanation:

Step 1:

@bitPos is used to...

I'm not sure if the logic you're trying to implement will help to achieve the goal you're looking for:

As far as I can see your EXIST statement is pretty much...

Hi,

to begin with a short advise: When posting xml data please use the relevant code tags (a list of IFCode Shortcuts is on the left side of the input box...

the cte (AKA as CommonTableExpression) basically is a subquery / derived table and could also be written as

SELECT id,

x.od.value('batch[1]','varchar(10)') AS batch,

x.od.value('cardexpirationdate[1]','VARCHAR(50)') AS cardexpirationdate,

y.c.value ('merchantRefCode[1]','VARCHAR(50)') AS merchantRefCode

FROM (SELECT id, cast (data...

Would something like this meet your requirement?

;WITH

cte AS (

SELECT id, cast (data AS xml) AS xml FROM #t

)

SELECT id,

x.od.value('batch[1]','varchar(10)') AS batch,

x.od.value('cardexpirationdate[1]','VARCHAR(50)') AS cardexpirationdate,

y.c.value ('merchantRefCode[1]','VARCHAR(50)') AS merchantRefCode

FROM cte

CROSS APPLY xml.nodes('payment') x(od)

CROSS...

Three things to notice:

a) you posted in a SQL2K5 forum so someone could expect...

Let's go for the next turn....

How about this version?

It returns all the values from the xml document rather than the selective ones you requested but I decided to leave it...

Would something like the following meet your (new) requirement in terms of showing the values (leaving out the ID for the moment)?

It's not exactly what you've requested but it follows...