How to convert this XML into columns
-
njdevils39 (3/23/2012)
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 employees in the employee table once you complete join statement.
I gave you guys an example of the first XML FILE In the rate_history.
The 4 columns I provided above or in previous posts is what I need the data to look like.
NO actually you NEVER provided any details about either the employee or employee_other table. How do you expect somebody to test the code if you can't provide that tables? You want us to provide you a query but we can't even begin to validate what we post.
If I run that query on my database here are the results...
Msg 208, Level 16, State 1, Line 1
Invalid object name 'employee'.
It is obvious you can't write this query when you have the tables and knowledge of the system. How can expect somebody else to put together a query without even having the tables?
_______________________________________________________________
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 (3/23/2012)
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 employees in the employee table once you complete join statement.
I gave you guys an example of the first XML FILE In the rate_history.
The 4 columns I provided above or in previous posts is what I need the data to look like.
You are right! It is the easy sample data query!
AND IF WE WOULD HAVE employee AND employee_other TABLES WITH DATA! We might be able to use it.
BUT WE DON'T HAVE THEM!
Instead of cutting every edge, you could read the article which everyone here does point you to and follow what this article says.
That would almost certainly guarantee fast and relevant help!
It is up to you at the end...
-
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 see is in XML FORMAT
When you click on the first XML it gives you what I already gave you above or in previous post.
-
Ah, I see! It is easy. We have done it before here. Link to solution:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
Can you expand my select statement to include the 4 columns I ask for from the rate_history field
-
njdevils39 (3/23/2012)
Can you expand my select statement to include the 4 columns I ask for from the rate_history fieldEdit: removed post
-
njdevils39 (3/23/2012)
Can you expand my select statement to include the 4 columns I ask for from the rate_history fieldNo offense meant but are you unable to read??? NO WE CAN'T!!!! It has been repeated over and over that we need tables to have a chance at helping you. I suggest you hire a consultant. It is painfully obvious you are unable to listen to very clear direction.
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.
I thought maybe you finally had a breakthrough when you said that but alas the post did not have any information still. You are now 36 posts and a week into people saying the same thing...GIVE US SOMETHING TO WORK WITH!!!!!!!!!
_______________________________________________________________
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 (3/23/2012)
Can you expand my select statement to include the 4 columns I ask for from the rate_history fieldYes, sure, no problem at all. It shows how to do exactly that here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
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, eo.rate_history
from
employee as em
join
employee_other as eo
on
em.emp_id = eo.emp_id
Here is what I have so far:
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,
TO_PAY_RATE,
CHANGED_BY
)
(
SELECT em.emp_id, em.emp_name, em.emp_organization, em.emp_organization_id, em.emp_department, DATE_CHANGED = (convert(xml(rate_history), rate, 101)),
FROM_PAY_RATE = (convert(xml(rate_history), rate, 101)), TO_PAY_RATE = (convert(xml(rate_history), rate, 101)),
CHANGED_BY = (convert(xml(rate_history), rate, 101))
from
employee as em join
employee_other as eo
on
em.emp_id = eo.emp_id
where em.emp_id = @emp_id and emp_organization_id = @ORG_LEVEL_ID
)
END
-
Seriously?? This is actually starting to be funny. I am interested at this point to see how many times you can be asked for the same information and continue to apologize for not providing it. We are nearly 40 posts into this and after repeated requests for ddl and sample data for the employee and employee_other tables we continue to not get it.
Here I will give you a skeleton..
create table employee
...you need to fill this part in
create table employee_other
...you need to fill this part in
insert employee
select ...you need to fill this part in
insert employee_other
select ...you need to fill this part in
Once you fill in the missing blanks, which is pretty much the entirety of your issue, we can begin to work on a solution.
It is still incredibly unclear what or where the xml portion of this issue is in relation to everything else.
_______________________________________________________________
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/ -
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 DATETIME,
FROM_PAY_RATE INT, TO_PAY_RATE INT, CHANGED_BY VARCHAR(255))
INSERT INTO #RATE_HISTORY
(EMP_NAME , EMP_ORGANIZATION , EMP_DEPARTMENT , EMP_DT_TERMINATION, DATE_CHANGED, FROM_PAY_RATE
, TO_PAY_RATE , CHANGED_BY )
SELECT e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
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 ) ,
TO_PAY_RATE = (select emp_id from employee where emp_id = @emp_id ),
CHANGED_BY = (select emp_id from employee where emp_id = @emp_id )
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
select * from #RATE_HISTORY
OK this is what I have and something works but not everything .
-
OK this is what I have and something works but not everything .
Well that certainly explains your portions that don't work...
That is kind of like saying "I have this drive with about 1TB of mp3s on it, and one of them skips. Can you help me find it?"
I am guessing these are a bit of an issue.
FROM_PAY_RATE = (select emp_id from employee where emp_id = @emp_id ) ,
TO_PAY_RATE = (select emp_id from employee where emp_id = @emp_id ),
CHANGED_BY = (select emp_id from employee where emp_id = @emp_id )
That is the same thing as
FROM_PAY_RATE = @emp_id,
TO_PAY_RATE = @emp_id,
CHANGED_BY = @emp_id
Which I suspect is not really what you are looking for. You should look at the columns you are selecting in your subquery. I don't think you even need a subquery for these.
_______________________________________________________________
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/ -
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 ) ,
TO_PAY_RATE = (select emp_id from employee where emp_id = @emp_id ),
CHANGED_BY = (select emp_id from employee where emp_id = @emp_id )
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
DATE_CHANGED = latest Date_changed from the XML FILE
FROM_PAY_RATE = Earliest rate from the XML FILE
TO_PAY_RATE = FINAL rate from the XML FILE
CHANGED_BY = Percentage of change between FROM_PAY_RATE and TO_PAY_RATE
OR, ALSO I need the whole XML Field in the code since its only for a single employee and organization.
Here is the XML FILE AGAIN
<Rates>
<Rate date_changed="Nov 10 2011 7:28AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 7:19AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:38AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:33AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<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>
-
43 posts and still no ddl or sample data for employee or employee_other. You have however yet again provided the xml and stated what you want out of it. I am beginning to think you actually don't want any help. You certainly are not even making an attempt at providing what is incredibly easy to post. I gave you a skeleton a few posts ago. Try again...
_______________________________________________________________
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/ -
Ok,
Please give me an example of a DDL ans Sample_Data I can post here so you can help me a bit?
Viewing 15 posts - 31 through 45 (of 194 total)
You must be logged in to reply to this topic. Login to reply