Like this

SELECT

A.*

FROM

OPENXML(@Payrollhandle,'/Payroll/Doctor/WorkDays/Office',8)

WITH (DoctorID INT '../../@ID',

StartDate DATE '../../../@StartDate',

EndDate DATE '../../../@EndDate',

WorkDaysOfficeID VARCHAR(10) '@ID',

WorkDays INT '.'--,

--AsstDaysOfficeID VARCHAR(10) 'AsstDays/Office/@ID',

--AsstDays INT 'AsstDays/Office' ,

--PTODaysOfficeID VARCHAR(10) 'PTODays/Office/@ID' ,

--PTODays INT 'PTODays/Office'

)

AS A

DECLARE @PayrollXML XML = CAST(@Payrolldoc AS XML)

SELECT doctor.r1.value('@ID','INT') AS DoctorID,

doctor.r1.value('../@StartDate','DATETIME') AS StartDate,

doctor.r1.value('../@EndDate','DATETIME') AS EndDate,

...

Yes, you just need to change the ORDER BY clause

;WITH Dups AS (

SELECT

[Patient_Count]

,[Read_code7]

,[Read_code]

,[Current_Caseload_Holder]

,[Staff_number]

,[Event_date]

,[Event_done_at],

ROW_NUMBER() OVER (PARTITION BY [Read_code7],[Read_code],[Staff_number] ORDER BY CASE WHEN [Current_Caseload_Holder] IS NOT NULL THEN 0 ELSE 1 END,...

Use ROW_NUMBER, have a look here http://www.sqlservercentral.com/articles/duplicate+data/102383/

SELECT para_value AS "PDTSHRTDE"

FROM Temp1

FOR XML PATH('PRODUCT'),ROOT('ROOT');

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Decide on a suitable time for them both to start and then in one window run this

WAITFOR TIME '13:00:00'

select * from table1

and run this in the other

WAITFOR TIME '13:00:00'

select *...

DataColumn col = dt.Columns[columnName];

George M Parker (10/18/2013)

Same here, wasn't familiar with the...

XML is generally case-sensitive so try changing

'Saa:Header/Saa:Message/saa:Format'."

to

'Saa:Header/Saa:Message/Saa:Format'."

SELECT A.*,CA.*

FROM @Temp A

OUTER APPLY(SELECT TOP 1 B.ReportType

FROM @Temp B

...

Amongst others and in no particular order

Numbers/tally table

Calendar tables

CROSS APPLY for PIVOTing

Gap and islands via ROW_NUMBER

Your XML isn't very clear, but see if this helps

DECLARE @x XML='

<root>

<INPUT>

<CID>17064</CID>

<OwnerID>17064</OwnerID>

<TaskId>0</TaskId>

<MSG>test...

Something like this?

DECLARE @x XML = '

<Root>

<device id ="40">

<sensor id = "256">

</sensor>

<sensor id = "258">

</sensor>

<sensor id = "259">

</sensor>

</device>

<device id ="51">

<sensor id = "1011">

</sensor>

<sensor id = "1012">

</sensor>

</device>

</Root>

'

SELECT x.device.value('@id[1]','INT') AS deviceID,

...

Use CROSS APPLY

DECLARE @t TABLE(c1 INT, c2 INT, c3 INT, c4 INT)

INSERT INTO @t(c1,c2,c3,c4)

VALUES(2,4,6,5);

SELECT ca.column1,ca.column2

FROM @t

CROSS APPLY(VALUES('c1',c1),('c2',c2),('c3',c3),('c4',c4)) ca(column1,column2);