How to convert this XML into columns

Question

How to convert this XML into columns

Viewing 15 posts - 76 through 90 (of 194 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

njdevils39 (3/26/2012)
capn.hector (3/26/2012)
njdevils39 (3/26/2012)
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.
We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
the sample data should be like this.
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'John Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Paul', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Mark Ryan', 'some other stuff','some more stuff', 'then the rest'
that way we can run the inserts to the temp table and have data to play with.
EDIT:
It is almost now a study how absolutely stubborn and pig headed an individual can be about not providing details to a problem they have requested to have help on. This thread may end up serving as the epitome of obstinate refusal to provide enough information to solve a problem.
:w00t::-D:-D:-D
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'Whitehead, Molly ', 'Silver Manor,Administrative', 'NULL' UNION ALL
SELECT 'Gentry, Clinton A ', Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Crosby, Sherman', 'Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Riddle, Nichole', 'Silver Manor','Maintenance', 'NULL'
FROM EMPLOYEE
INSERT INTO #EMPLOYEE_OTHER
SELECT '<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
FROM EMLOYEE_OTHER
NOTE: USE THE DDL FROM ABOVE
[/code]

That's nice. Please note that your code will fail as there is nothing to join the tables employee and employee_other.

May I suggest that you setup an empty sandbox database and run all your code in that database to ensure it all works?

Personally, I am not going to even attempt to run anything I know will fail based on a visual scan of the code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here is the from clause in your stored procedure:

...

FROM employee e

JOIN employee_other eo ON e.emp_id = eo.emp_id

...

I don't see emp_id in either of the create table statements you provided.

I hope you are taking the time to put together working scripts that will create the tables, populate the tables with valid sample data, and which your stored proce will successfully run against. This is where an empth sandbox database is invaluable.

Be sure to also provide us with the expected results based on the sample data so we have some to test against.

njdevils39 SSCrazy Points: 2676 More actions · Answer 3

OK, here is my DDL and Sample Date, but yes the tables are not joining up. Again sorry for kindergarten type mistakes I may still be making. But this Sample Data in employee_other table matches the employees in the Employee table I provided.

CREATE TABLE #EMPLOYEE

(

EMP_NAME VARCHAR(MAX),

EMP_ORGANIZATION VARCHAR(MAX),

EMP_DEPARTMENT VARCHAR(MAX),

EMP_DT_TERMINATION DATETIME

)

CREATE TABLE #EMPLOYEE_OTHER

(

RATE_HISTORY XML

)

INSERT INTO #EMPLOYEE

(

EMP_NAME,

EMP_ORGANIZATION,

EMP_DEPARTMENT,

EMP_DT_TERMINATION

)

(SELECT 'Whitehead, Molly ', 'Silver Manor,Administrative', 'NULL' UNION ALL

SELECT 'Gentry, Clinton A ', 'Silver Manor','Administrative', 'NULL' UNION ALL

SELECT 'Crosby, Sherman', 'Silver Manor','Administrative', 'NULL' UNION ALL

SELECT 'Riddle, Nichole', 'Silver Manor','Maintenance', 'NULL'

FROM EMPLOYEE)

INSERT INTO #EMPLOYEE_OTHER

(

RATE_HISTORY

)

(SELECT '<Rates>

<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

FROM EMPLOYEE_OTHER

)

SELECT * FROM #EMPLOYEE , #EMPLOYEE_OTHER

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

OK we are finally getting somewhere...

I cleaned up your sample data so that other can actually use it. Your columns didn't quite match in the first set of queries and you still had NULL wrapped as a string. And of course you don't need to select FROM a table when using hardcoded inserts.

Here is the clean version.

CREATE TABLE #EMPLOYEE

(

EMP_NAME VARCHAR(MAX),

EMP_ORGANIZATION VARCHAR(MAX),

EMP_DEPARTMENT VARCHAR(MAX),

EMP_DT_TERMINATION DATETIME

)

CREATE TABLE #EMPLOYEE_OTHER

(

RATE_HISTORY XML

)

INSERT INTO #EMPLOYEE

(

EMP_NAME,

EMP_ORGANIZATION,

EMP_DEPARTMENT,

EMP_DT_TERMINATION

)

(SELECT 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

)

INSERT INTO #EMPLOYEE_OTHER

(

RATE_HISTORY

)

(SELECT '<Rates>

<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT

'<Rates>

<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

)

SELECT * FROM #EMPLOYEE

select * from #EMPLOYEE_OTHER

drop table #EMPLOYEE

drop table #EMPLOYEE_OTHER

Now there is still one incredibly important piece missing. We now have an #EMPLOYEE_OTHER table but there is nothing there to tie that information back to an employee. Should you have an EmployeeID in both tables or something along those lines?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

njdevils39 SSCrazy Points: 2676 More actions · Answer 5

OK, here are the EMP_ID's for all of these 4 employees.

/* SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole') */

CREATE TABLE #EMPLOYEE2

(

EMP_ID INT,

EMP_NAME VARCHAR(MAX),

EMP_ORGANIZATION VARCHAR(MAX),

EMP_DEPARTMENT VARCHAR(MAX),

EMP_DT_TERMINATION DATETIME

)

CREATE TABLE #EMPLOYEE_OTHER2

(

EMP_ID INT,

RATE_HISTORY XML

)

INSERT INTO #EMPLOYEE2

(

EMP_ID,

EMP_NAME,

EMP_ORGANIZATION,

EMP_DEPARTMENT,

EMP_DT_TERMINATION

)

(SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

)

INSERT INTO #EMPLOYEE_OTHER2

(

EMP_ID,

RATE_HISTORY

)

(SELECT '3' , '<Rates>

<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT '5' ,

'<Rates>

<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT '6' ,

'<Rates>

<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

UNION ALL

SELECT '7' ,

'<Rates>

<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>'

)

SELECT * FROM #EMPLOYEE2

select * from #EMPLOYEE_OTHER2

drop table #EMPLOYEE2

drop table #EMPLOYEE_OTHER2

njdevils39 SSCrazy Points: 2676 More actions · Answer 6

Here is the photo of the return data from my last statement.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Does this help?

SELECT e.EMP_ID,

e.EMP_NAME,

e.EMP_ORGANIZATION,

e.EMP_DEPARTMENT,

e.EMP_DT_TERMINATION,

x.r.value('@date_changed','DATETIME') AS date_changed,

x.r.value('@not_active_since','DATETIME') AS not_active_since,

x.r.value('@rate','DECIMAL(10,5)') AS rate,

x.r.value('@emp_pay_type','INT') AS emp_pay_type,

x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,

x.r.value('@rate2','DECIMAL(10,5)') AS rate2,

x.r.value('@rate3','DECIMAL(10,5)') AS rate3,

x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,

x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind

FROM #EMPLOYEE2 e

INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

njdevils39 SSCrazy Points: 2676 More actions · Answer 8

Mark-101232 (3/30/2012)
Does this help?
SELECT e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
x.r.value('@date_changed','DATETIME') AS date_changed,
x.r.value('@not_active_since','DATETIME') AS not_active_since,
x.r.value('@rate','DECIMAL(10,5)') AS rate,
x.r.value('@emp_pay_type','INT') AS emp_pay_type,
x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,
x.r.value('@rate2','DECIMAL(10,5)') AS rate2,
x.r.value('@rate3','DECIMAL(10,5)') AS rate3,
x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,
x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

This is good, but I expect, when the data is combined that the data is in one row like I have posted in my Stored_Procedure:

FOR Molly Whitehead

DATE_CHANGED = '3/6/2011'

FROM_PAY_RATE = '1730.77'

TO_PAY_RATE = '1730.77'

That is how the last 3 columns should look like in that row.

Here is the STORED PROC 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 VARCHAR(255), EMP_ORGANIZATION VARCHAR(255), EMP_DEPARTMENT VARCHAR(255), EMP_DT_TERMINATION DATETIME, DATE_CHANGED DATETIME,

FROM_PAY_RATE NVARCHAR(MAX), TO_PAY_RATE NVARCHAR(MAX))

INSERT INTO #RATE_HISTORY

(EMP_NAME , EMP_ORGANIZATION , EMP_DEPARTMENT , EMP_DT_TERMINATION, DATE_CHANGED, FROM_PAY_RATE

, TO_PAY_RATE)

SELECT e.emp_name,

e.emp_organization,

e.emp_department,

e.emp_dt_termination,

DATE_CHANGED = (select max(convert(varchar(10), x.r.value('@date_changed','DATETIME'), 101)) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),

FROM_PAY_RATE = (select min(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),

TO_PAY_RATE = (select max(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r))

FROM employee e

JOIN employee_other eo ON e.emp_id = eo.emp_id

where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID

select * from #RATE_HISTORY

END

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Like this?

select * from

(

SELECT

ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc) as RowNum,

e.EMP_ID,

e.EMP_NAME,

e.EMP_ORGANIZATION,

e.EMP_DEPARTMENT,

e.EMP_DT_TERMINATION,

x.r.value('@date_changed','DATETIME') AS date_changed,

x.r.value('@not_active_since','DATETIME') AS not_active_since,

x.r.value('@rate','DECIMAL(10,5)') AS rate,

x.r.value('@emp_pay_type','INT') AS emp_pay_type,

x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,

x.r.value('@rate2','DECIMAL(10,5)') AS rate2,

x.r.value('@rate3','DECIMAL(10,5)') AS rate3,

x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,

x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind

FROM #EMPLOYEE2 e

INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

) x where RowNum = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

njdevils39 SSCrazy Points: 2676 More actions · Answer 10

Sean Lange (3/30/2012)
Like this?
select * from
(
SELECT
ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc) as RowNum,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
x.r.value('@date_changed','DATETIME') AS date_changed,
x.r.value('@not_active_since','DATETIME') AS not_active_since,
x.r.value('@rate','DECIMAL(10,5)') AS rate,
x.r.value('@emp_pay_type','INT') AS emp_pay_type,
x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,
x.r.value('@rate2','DECIMAL(10,5)') AS rate2,
x.r.value('@rate3','DECIMAL(10,5)') AS rate3,
x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,
x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1

SOMETHING LIKE THAT, but not as many columns

But focus on this area in my stored procedure

DATE_CHANGED = (select max(convert(varchar(10), x.r.value('@date_changed','DATETIME'), 101)) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),

FROM_PAY_RATE = (select min(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),

TO_PAY_RATE = (select max(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r))

This will give me a result which looks like this, but the data in the last 3 columns is wrong:

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

This will give me a result which looks like this, but the data in the last 3 columns is wrong:

What does that mean? How about if you post what you want the data to look like?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

njdevils39 SSCrazy Points: 2676 More actions · Answer 12

Sean Lange (3/30/2012)
This will give me a result which looks like this, but the data in the last 3 columns is wrong:
What does that mean? How about if you post what you want the data to look like?

HERE IS THE XML FILE FOR Molly Whitehead:

<Rates>

<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

</Rates>

EMP_ID = '3'

EMP_NAME = 'Whitehead, Molly'

EMP_ORGANIZATION = 'Silver Manor'

EMP_DEPARTMENT = 'Administrative'

EMP_DT_TERMINATION = 'NULL'

DATE_CHANGED = '3/6/2011' No Time Needed

FROM_PAY_RATE = '1730.77'

TO_PAY_RATE = '1730.77'

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Well you have been handed 99% of this. Why don't give it a try to go the last 10 feet? Pretty sure you can the columns you want. It seems the only piece that was not handed to you on a silver platter is the "previous" rate. At least I think it is the previous rate?

Give it a shot and get it close then post back with whatever pieces you can't get.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

njdevils39 SSCrazy Points: 2676 More actions · Answer 14

The problem I've been getting is trying to get the correct date, and getting the value from the rate column in the XML table.

Sean Lange SSC Guru Points: 286590 More actions · Answer 15

What do you have so far?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/