How to convert this XML into columns
-
How do you convert this XML file into columns, which includes date_changed, rate, changed_by(percentage), from earliest date to later date.
<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>
-
This will give the XML as columns. You'll need to give more information about how to calculate the other columns you're after.
DECLARE @x XMLSET @x='<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>'
SELECT 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 @x.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
-
Name:
WEB_HR_GetRateHistory
This is the procedure I have to write and it has to include only date_changes, From Pay Rate, To Pay Rate and Changed By.
Note: The columns above are combined in an XML file for the column rate_history in (employee_other) table
Parameters:
@org_level_id, @emp_id
If @emp_id is 0 – get me all rate change history for all active employees within this org_level
Tables:
select * from employee
select * from employee_other
Resultset:
Employee Name
Center
Department
--Date
From Pay Rate
To Pay Rate
Changed by
-
i know how to join the two tables, but need help with the XML column.
-
Name:
WEB_HR_GetRateHistory
Parameters:
@org_level_id, @emp_id
If @emp_id is 0 – get me all rate change history for all active employees within this org_level
Tables:
select * from employee
select * from employee_other
Resultset:
Employee Name
Center
Department
--Date
From Pay Rate
To Pay Rate
Changed by
Note: Date, FROM PAY RATE, TO PAY RATE, Changed by are all in XML format together in the column in the employee_other table named (rate_history).
Note: emp-name, center and departments are easy columns to retrieve from employee table.
Here is how the rate history column looks and sample xml file from one of the boxes in the column:
<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>
-
The reason nobody has responded to this thread is you haven't provided enough details to even begin to help. The explanation you provided is incredibly vague and their is no supporting ddl or sample data. Take a look at the first link in my signature for best practices on posting questions.
Ask yourself if you would be able to put together a query based on the information you provided. Basically we know you have been told to write a proc, we know the name (WEB_HR_GetRateHistory). Then we have some xml and a couple table names.
_______________________________________________________________
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/ -
In the column(employee_other table) pictured above, there are a bunch of XML files, need to take those XML files and convert it into the 4 columns mentioned above.
-
The first example explains quite easily how to convert your xml into a table. Not sure what else you are missing.
_______________________________________________________________
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/ -
Still not clear what you're asking. If your XML is in a table, you can use CROSS APPLY to get the values.
SELECT 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 employee_other
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 -
but I needed for all employees, not just the one field.
-
njdevils39 (3/19/2012)
but I needed for all employees, not just the one field.I take it you did not bother to read the article I pointed you to. You still have not provided enough information. How can anybody help you write a query when we don't what the table looks like or what some sample data might look like. Obviously you need to change some details to obfuscate the real details.
_______________________________________________________________
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 apologize Sean,
but can you please give me an idea or code example how you would go about writing this stored procedure:
Note: Please include emp_name, emp_organization and emp_department from employee table.
But from the XML field I only need (Date_CHanged, From Rate, to Rate, Changed By). Not all the columns.
Hopefully this is not vague.
-
njdevils39 (3/19/2012)
I apologize Sean,but can you please give me an idea or code example how you would go about writing this stored procedure:
Note: Please include emp_name, emp_organization and emp_department from employee table.
But from the XML field I only need (Date_CHanged, From Rate, to Rate, Changed By). Not all the columns.
Hopefully this is not vague.
Cross apply your xml to your 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/ -
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.
-
njdevils39 (3/19/2012)
I apologize Sean,but can you please give me an idea or code example how you would go about writing this stored procedure:
Note: Please include emp_name, emp_organization and emp_department from employee table.
But from the XML field I only need (Date_CHanged, From Rate, to Rate, Changed By). Not all the columns.
Hopefully this is not vague.
If you want tested code, read and follow the instructions in the article Sean recommended. The more you help us help you, the better the answers you will get.
Viewing 15 posts - 1 through 15 (of 194 total)
You must be logged in to reply to this topic. Login to reply