Ok, then we'd need to add the namespace declaration (result as above).

Regarding your comment about the person sending you the file, here's what I'd say:

Whoever sent you that file should...

Here's a short example to query data like you've posted (please note that I added a few end tags to change it into a valid xml structure as per SQL...

dpalani (1/11/2011)

---

Thanks

in the example i have said three columns, But i have more than 20 rows to be tranfered to column, And the data is huge. it will hit...

It looks like a job for the CrossTab method.

Please see the related link in my signature.

pwalter83 (1/11/2011)

---

Hi Lutz,

I really have no answer to your questions and am stuck with no option but to resign from my current position here.

I actually made a bad choice when...

Another method would be a CrossTab query.

For details please see the related link in my signature.

pwalter83 (1/11/2011)

---

Hello LutzM and Jeff,

Thanks a lot for your replies. One thing I forgot to mention is that this project is part of an SSIS package. If you are...

I still think it's better to use th additional column (as suggested before), maybe in combination with the exception table I mentioned.

But the main issue still remains: how tro identify...

The sample data you provided aren't XML data. Those are HTML data.

As per http://www.w3schools.com/Xml/xml_whatis.asp

XML is not a replacement for HTML.

XML and HTML were designed with different goals:

...

Change your heavy WHERE clause to

INNER JOIN Categories_Products_Link

ON p.ProductID = Categories_Products_Link.ProductID

WHERE

Categories_Products_Link.CategoryID IN (21,219,...)

I'd also recommend to consider using an additional table to hold those numerous numbers if those...

Here's a test scenario showing that your code should work.

Beside the table name, what's different on your end?

DECLARE @tbl TABLE

(

ID INT,R_DATE DATETIME, SHRNCRS_NCST_CODE CHAR(2)

)

INSERT INTO @tbl

SELECT...

Paul,

I'm not sure if I completely understood the logic behind your CASE statements. But to me it looks like you could add a (persisted) computed column to your DHL_TRADE_ASSIGNMENT table...

Strange. Should work. Is this the full query you posted?

Wouldn't the following return the same result?

;WITH cte AS

(

SELECT

ID,

R_DATE,

CATEGORY,

ROW_NUMBER() OVER (PARTITION BY SHRNCRS_NCST_CODEORDER BY R_DATE DESC) Rno

FROM STABLE

WHERE SHRNCRS_NCST_CODE IN('CC', 'MB')

)

SELECT *

FROM cte

WHERE Rno=1

You could either use a subquery with MAX() and GROUP BY or change the PARTITION BY part in your ROW_NUMBER subquery and use it directly against your source table.

As a...