It also might help to preaggregate the tables commonly used in both UNION statements (e.g. using a temp table, maybe even a CTE will help) as well as considering UNION...

I'm not sure if a forum post...

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?