create table #RATEHISTORY

(EMP_NAME VARCHAR(255), EMP_ORGANIZATION VARCHAR(255), EMP_DEPARTMENT VARCHAR(255), EMP_DT_TERMINATION DATETIME, DATE_CHANGED DATETIME,

FROM_PAY_RATE INT, TO_PAY_RATE INT, CHANGED_BY VARCHAR(255))

insert into #RATEHISTORY

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

, TO_PAY_RATE...

Ok,

Please give me an example of a DDL ans Sample_Data I can post here so you can help me a bit?

DATE_CHANGED = (SELECT x.r.value('@date_changed','DATETIME') from employee_other,

FROM_PAY_RATE = (select emp_id from employee where emp_id = @emp_id ) ,

...

ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]

(

@ORG_LEVEL_ID INT,

@EMP_ID INT

)

AS

declare @xmlDoc varchar(max)

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...

I apologize over and over again, but here is what I have so far:

Shortened, but incorrect version is image I gave you, but here is statement:

select em.emp_name, em.emp_organization, em.emp_department,...

Can you expand my select statement to include the 4 columns I ask for from the rate_history field

OK since you guys cannot access the server I am writing this procedure on, Here are first few rows of the result set in basic format.

Now rate_history as you...

This is the easy sample data query:

select em.emp_name, em.emp_organization, em.emp_department, eo.rate_history

from

employee as em

join

employee_other as eo

on

em.emp_id = eo.emp_id

Now rate_history column is in XML FORMAT in the employee_other table for all...

DDL STATEMENT:

CREATE TABLE #RATE_HISTORY

(

EMP_NAME VARCHAR(255),

EMP_ORGANIZATION VARCHAR(255)

EMP_DEPARTMENT VARCHAR(255),

DATE_CHANGED DATETIME,

FROM_PAY_RATE INT,

TO_PAY_RATE INT,

CHANGED_BY INT

)

SAMPLE DATA:

INSERT INTO #RATE_HISTORY

(

EMP_NAME,

EMP_ORGANIZATION,

EMP_DEPARTMENT,

DATE_CHANGED,

FROM_PAY_RATE,

TO_PAY_RATE,

CHANGED_BY

)

(

SELECT em.emp_name, em.emp_organization, em.emp_department, eo.DATE_CHANGED = READ_ABOVE, eo.FROM_PAY_RATE = READ_ABOVE,...

I want 4 columns based on not only this XML code, but all the XML codes in the column RATE_HISTORY which is in XML format.

DATE_CHANGED: The latest date in the...

Yes I know, but how do I put that CROSS APPLY STATEMENT YOU GUYS SUGGESTED AND JOIN It with the employee table.

Here is my code so far: How should I continue?

ALTER PROCEDURE WEB_HR_GetRateHistory

(

@ORG_LEVEL_ID INT,

@EMP_ID INT

)

AS

BEGIN

CREATE TABLE #RATE_HISTORY

(

EMP_NAME VARCHAR(255),

EMP_ORGANIZATION VARCHAR(255)

EMP_DEPARTMENT VARCHAR(255),

DATE_CHANGED DATETIME,

FROM_PAY_RATE INT,

TO_PAY_RATE INT,

CHANGED_BY INT

)

INSERT INTO #RATE_HISTORY

(

EMP_NAME,

EMP_ORGANIZATION,

EMP_DEPARTMENT,

DATE_CHANGED,

FROM_PAY_RATE,...

-Say From_PAY_Rate on March 1st would say 3.00 just to give in example

-Say To_Pay_Rate on latest date as it is shown in the XML file is 3.20 for example I...

This is my join statement, but the rate_history columns has to be broken down into 4 columns (Date_Changed, FROM_PAY_RATE, TO_PAY_RATE, CHANGED_BY_PERCENTAGE). How do I go about doing this with the...

Please give me a code example combining the two tables and data sets.

I only need 4 columns from the employee-Other table. Sorry for being annoying or vague.