OP is Original Poster.

opc.three (3/3/2011)

---

drew.allen (3/3/2011)

---

opc.three (3/3/2011)

---

Try this:

SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],

fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],

fr.value('(newStandPrice)[1]', 'decimal')...

opc.three (3/3/2011)

---

Try this:

SELECT b.value('(offerId)[1]', 'Int') AS [offer_id],

fr.value('(internetPrice)[1]', 'decimal') AS [internet_price],

fr.value('(newStandPrice)[1]', 'decimal') AS [news_stand_price],

...

You're shredding the same document twice when you really don't need to, because you started from the highest relevant point in the document hierarchy and worked down, when you really...

Jeff Moden (3/2/2011)

---

drew.allen (3/2/2011)

---

Another option is to use the LIKE operator. I don't know which will perform better.

SELECT *

FROM TableA

INNER JOIN TableB

ON TableA.ID LIKE Cast(TableB.ID as varchar(11)) + '%'

This...

Another option is to use the LIKE operator. I don't know which will perform better.

SELECT *

FROM TableA

INNER JOIN TableB

ON TableA.ID LIKE Cast(TableB.ID as varchar(11)) + '%'

This assumes that the...

Do you really want to return fiscal year plus calendar month rather than fiscal year plus fiscal period?

I also dislike using a CASE statement to return the fiscal year, because...

Yes, you're using a CURSOR when you should be using a set-based approach. I can't tell you the exact set-based approach to...

What I've done in these cases is use a TRY/CATCH block leaving the CATCH block empty.

BEGIN TRY

EXEC msdb.dbo.sp_send_dbmail <your parameters here>

END TRY

BEGIN...

The problem with your original CASE statement is that you were trying to return a Boolean value and T-SQL won't let you return a Boolean value. You can restructure...

You can also rewrite this using a single CASE statement, which is usually more succinct and therefore easier to follow.

Select

case when SO.CUSTOMER = R.CUSTOMER AND R.QUANTITY_REMAINING >= R.CUSTOMER_QTY_NOT_SHIPPED

then...

Jeff Moden (2/1/2011)

---

drew.allen (1/31/2011)

---

My tests indicate the...

My tests indicate the opposite. I've...

What have you tried already and where are you running into problems? I'd recommend looking at Row_Number() or other ranking functions.

Drew

krishusavalia (1/31/2011)

---

Garadin,

I appriciate your fast responce.

But in my actual xml file i have name space so in the code i am declaring the name space.

;WITH XMLNAMESPACES(default 'http://schemas.xmlaaaa.org/aaaaenv/envelope/')

so when i am...