How to convert this XML into columns
-
njdevils39 (3/26/2012)
Ok,Please give me an example of a DDL ans Sample_Data I can post here so you can help me a bit?
No problem. Look at the first link in my signature. It explains it quite clearly. You can also look at my last post on page 4. http://www.sqlservercentral.com/Forums/FindPost1271977.aspx It is a skeleton of what is needed specific to your situation.
_______________________________________________________________
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/ -
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 , CHANGED_BY )
(
SELECT e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select date_changed from employee_other..rate_history where date_changed = 'get_date()' ),
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) and e.emp_organization_id = @ORG_LEVEL_ID
)
I decided to put in the join statement for both tables.
-
You have posted this before...step back and ask yourself if you think I can possibly gain anything from that. I continue to ask for either ddl and sample data from employee and employee_other OR provide with actual insert statements. I can't run your insert because I DON'T HAVE THOSE TABLES!!!!
With at least a dozen suggestions to read the article about posting questions I don't think you have bothered yourself yet to take 5 minutes of your precious time to provide details so the volunteers on this site can help you.
There have been a number of people in this thread that have all suggested the same thing over and over. I am the only person left that is even responding. I suggest you post something useful before you lose your last resource on here.
_______________________________________________________________
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/ -
create table employee
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255),
EMP_DEPARTMENT VARCHAR(255),
EMP_DT_TERMINATION DATETIME
)
create table employee_other
(
DATECHANGED DATETIME,
FROM_PAY_RATE INT,
TO_PAY_RATE INT,
CHANGED_BY VARCHAR(255)
)
insert into employee
SELECT emp_name,
emp_organization,
emp_department,
emp_dt_termination
FROM EMPLOYEE
insert employee_other
select rate_history(date_changed, from_pay_rate, to_pay_rate, changed_by) from employee_other
-
PROGRESS!!!! We finally have table definitions, and only 49 posts in. Now if you could just please provide a few rows of sample data...the inserts you posted don't exactly do anything.
insert employee
select 'some hard coded sample values' union all
select 'some hard coded sample values'
insert employee_other
select 'some hard coded sample values' union all
select 'some hard coded sample values'
_______________________________________________________________
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/ -
This seems harder than the prototype I created, all I have to do is join the two tables, but only extract items from the rate_history XML column.
Give me a picture of what you are asking for, because this seems confusing for what you provided.
-
njdevils39 (3/26/2012)
This seems harder than the prototype I created, all I have to do is join the two tables, but only extract items from the rate_history XML column.Give me a picture of what you are asking for, because this seems confusing for what you provided.
OK spoon feeding even isn't enough here. Let me try to give a picture of your scenario and your request.
You are trying to create a stored proc to hold some sort of history.
In this proc you somehow have some xml that you want in a table.
Also in this proc you want to insert into a temp table. This insert is where you are having problems.
INSERT INTO #RATE_HISTORY
select some data and cross apply the xml.
What you are looking for is for somebody to help you make this insert work. Nobody can help you create that insert unless they have the source table.
_______________________________________________________________
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/ -
The temp table is the join of two tables, the employee table and the employee_other table. There is no source table in this stored proc.
Its a simple join statement of two tables, I already gave you a photo of how the easy way to approach the query. Maybe we are no on the same wavelength here, but here is the easy data retrieve from this simple 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
http://a8.sphotos.ak.fbcdn.net/hphotos-ak-ash3/526990_10151428765230529_733725528_23305953_234922836_n.jpg">
-
njdevils39 (3/26/2012)
The temp table is the join of two tables, the employee table and the employee_other table. There is no source table in this stored proc.Its a simple join statement of two tables, I already gave you a photo of how the easy way to approach the query. Maybe we are no on the same wavelength here, but here is the easy data retrieve from this simple 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
And it is in this query you need your xml data right?
_______________________________________________________________
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/ -
I am not having any trouble retrieving data from the employee table. Its the Rate_history column I am having trouble with in employee_other table.
-
I know but it is this insert right? You are trying to insert this data and 2-3 of the columns data comes from your xml?
_______________________________________________________________
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/ -
Yes and I gave you an example of the How the Columns from the XML should be structured
Date CHanged = "mOst recent Date"
From Pay_Rate = 'initial rate from Earliest Date'
To_Pay_Rate = 'Most Recent Date Pay Rate'
CHanged_BY = 'Difference from the two columns'
-
Yes but do you want to me to just blindly think I might have it right or would you prefer I actually test my code? I would assume you want the latter, that means I need at the very the employee_other sample data. I can't validate a query if I don't have anything to validate it with. I don't why you are so obstinate about providing 3-4 rows of sample data. It doesn't have to be real data, in fact it shouldn't be real data. But at the very least you should provide some sample data that corresponds to the xml you want to parse. I have grown quite tired of asking over and over. I have spent nearly a whole day of my time for free trying to help you get paid for your job. This is really a simple thing that could have been resolved two weeks ago and about 20 minutes of effort on my part. Maybe somebody else will come along and help IF you actually provide the information requested.
_______________________________________________________________
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 here we go, here is the photo of the result set that I need, but the last three columns need to be other numbers from the XML files in the employee_other table.
-
Pictures??? They are not even attached to your post. Even if they were they wont help. What will help is sample data for the employee_other table. Looking back at the ddl it appears to be incomplete. There is nothing in the employee_other table to know which employee row it belongs to etc..
At best you have posted partial information.
_______________________________________________________________
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/
Viewing 15 posts - 46 through 60 (of 194 total)
You must be logged in to reply to this topic. Login to reply