Almost. But the written request does not match the requested output (this is exactly why we always ask for example output) : the group by should be on caseid AND...

Have you tried reducing the 4 updates into a single one, possibly even using the merge statement to do any needed insert/delete actions in one single pass too?

And what sort...

One thing I see hardly addressed in SSRS is translations. i.e. provide the same data to different users in the same layout but all fixed texts on the report are...

dwain.c (5/24/2012)

---

Mark-101232 (5/24/2012)

---

Try this, I can't see a simpler way with the XML as is.

WITH CTE AS (

SELECT row.value('local-name(.)', 'VARCHAR(50)') AS name

,row.value('text()[1]', 'VARCHAR(50)') AS...

Of course the xml should be improved on for any practical implementation, but still this challenged me. Since this is hypothetical already and on top of that Mark is in...

You've effectively reduced your chances on getting the answer you need to 50% by putting the actual question and the data with it in an attachment: Your question has had...

Mark-101232 (5/23/2012)

---

You can also use preceding-sibling for this

select p.p.value('1+count(for $a in . return $a/../*[. << $a])','int') as parentID,

p.p.value('@name','varchar(10)') as parentName,

c.c.value('1+count(for $a in . return...

And your point is? Because the output of your query is:

<test>

<value1>A</value1>

<value2>B</value2>

</test>

Which is not the same as the required output:

<test>

<value>A</value>

<value>B</value>

</test>

The workaround suggested by Microsoft is to put a zero-length string in between the both elements:

select

null [value],

'',

null [value]

for xml path('test'), type, elements xsinil

which produces:

<test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<value xsi:nil="true" />

...

Yeah, that was my workaround too. But I still think it is a bug though. I've reported it on connect: http://connect.microsoft.com/SQLServer/feedback/details/742314/for-xml-path-concatenates-elements-with-the-same-name. Please vote it up if you also think it...

njdevils39 (5/7/2012)

---

...

Ok, now it works fine for every employee. But can you tell me if you can, how to do it with this code:

ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]

(

@ORG_LEVEL_ID INT,

@EMP_ID INT

)

AS

BEGIN

DECLARE @x XML;

...

njdevils39 (5/2/2012)

---

So, in my post to go off from this code:

ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]

(

@ORG_LEVEL_ID INT,

@EMP_ID INT

)

AS

BEGIN

DECLARE @x XML;

CREATE TABLE #RATE_HISTORY

(

EMP_NAME...

I don't have a script to identify the rights one specific user/login has, but I do have this script that scripts out all rights defined per object (if run by...

Something like this?

with cteRates as (

select o.emp_id,

row_number() over (partition by o.emp_id order...

@ryan-2: You're wrong and have clearly not read any of the other comments posted already. Better alternatives have been presented and your additions have...