December 21, 2010 at 10:39 am
Hi, I have a table and wanted the results to be in XML format as below and my query is
select * from empoyees
order by emp_code
and below is the xml format that it should look like. I am new to XML and don't know the approach. Could any one please let me know...how to accomplish this. Many thanks!!!
<?xml version = "1.0" encoding="UTF-8" ?>
<!DOCTYPE empload (View Source for full doctype...)>
<empload global_version="1.0">
<entry emp_cd="0140" last_name="Passela" first_name="Lucia" user_name="4400" hiredate="01/01/2010" birthdate="01/01/1980" >
<position code="1010" primary="Y" org_cd="5156" org_level_cd="STORE" eff_date="01/01/2010" />
<status code="ACTIVE" eff_date="01/01/2010" />
<payrate value="20.5" unit_cd="HOUR" type_cd="BASE" eff_date="01/01/2010"/>
<schedplan code="NYFTH" eff_date="01/01/2010"/>
<payplan code="NYFTH" eff_date="01/01/2010"/>
</entry>
<entry emp_cd="50006" last_name="Maloney" first_name="Ellen" user_name="560506" hiredate="01/01/2010" birthdate="01/01/1980" >
<position code="1000" primary="Y" org_cd="5156" org_level_cd="STORE" eff_date="01/01/2010" />
<status code="ACTIVE" eff_date="01/01/2010" />
<payrate value="16.16" unit_cd="HOUR" type_cd="BASE" eff_date="01/01/2010"/>
<schedplan code="NYFTH" eff_date="01/01/2010"/>
<payplan code="NYFTH" eff_date="01/01/2010"/>
</entry>
<entry emp_cd="564449" last_name="Collins" first_name="Laura" user_name="565449" hiredate="01/01/2010" birthdate="01/01/1980" >
<position code="1090" primary="Y" org_cd="5156" org_level_cd="STORE" eff_date="01/01/2010" />
<status code="ACTIVE" eff_date="01/01/2010" />
<payrate value="21.9" unit_cd="HOUR" type_cd="BASE" eff_date="01/01/2010"/>
<schedplan code="NYFTH" eff_date="01/01/2010"/>
<payplan code="NYFTH" eff_date="01/01/2010"/>
</entry>
</empload>
December 21, 2010 at 2:49 pm
Please post table def and (fake) sample data matching your expected result as described in the first link in my signature.
Some of us (including me) would like to test our solution before posting.
December 21, 2010 at 7:36 pm
Here's the table def and values
Create table employee
(emp_cd char(16) NOT NULL
, last_name char(64) NULL
, first_name char(32) NULL
, middle_name char(32) NULL
, address1 char(40) NULL
, address2 char(40) NULL
, city char(48) NULL
, [state] char(16) NULL
, zip char(15) NULL
, country char(32) NULL
, user_name char(16) NULL
, hiredate char(10) NULL
, birthdate char(10) NULL
, email char(128) NULL
, position_code char(12) NOT NULL
, primary char(1) NOT NULL
, org_cd char(12) NOT NULL
, org_level_cd char(12) NOT NULL
, eff_date char(10) NOT NULL
, status_code char(12) NOT NULL
, status_eff_date char(10) NOT NULL
, payrate_value decimal(16,6) NOT NULL
, unit_cd char(12) NOT NULL
, type_cd char(12) NOT NULL
, payrate_eff_date char(10) NOT NULL
, schedplan_code char(12) NOT NULL
, sched_eff_date char(10) NOT NULL
, payplan_code char(12) NOT NULL
, payplan_eff_date char(10) NOT NULL)
Insert into employee (emp_cd, last_name, first_name, middle_name, address1, address2, city,[state], zip, country, user_name, hiredate, birthdate, email
, position_code, primary, org_cd, org_level_cd, eff_date
, status_code, status_eff_date
, payrate_value, unit_cd, type_cd, payrate_eff_date
, schedplan_code, sched_eff_date
, payplan_code, payplan_eff_date)
Values
(1010, 'Passela', 'Lucia',,,,,,,,,,,4400,'01/01/2010','01/01/1980',,
1010,'Y',5156,'STORE','01/01/2010',
'Active','01/01/2010',
20.5,'HOUR','BASE','01/01/2010',
'NYFTH','01/01/2010'
'NYFTH','01/01/2010'),
(50006,'Maloney','Ellen',,,,,,,,,,,560506,'01/01/2010','01/01/1980',,
1000,'Y',5156,'STORE','01/01/2010',
'ACTIVE','01/01/2010',
16.16,'HOUR','BASE','01/01/2010',
NYFTH,'01/01/2010',
NYFTH,'01/01/2010'),
(564449,'Collins','Laura',,,,,,,,,,,565449,'01/01/2010','01/01/1980',,
1090,'Y',5156,'STORE',01/01/2010',
'ACTIVE','01/01/2010',
21.9,'HOUR','BASE','01/01/2010',
NYFTH,'01/01/2010',
NYFTH,'01/01/2010'),
Thanks!
December 21, 2010 at 8:38 pm
lsurapaneni (12/21/2010)
Here's the table def and values
I take it that you're using SQL 2008 - R1 or R2?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 21, 2010 at 8:40 pm
I am using SQL 2005
December 21, 2010 at 8:46 pm
lsurapaneni (12/21/2010)
I am using SQL 2005
Really? With an INSERT statement that has a VALUES clause like that?
BTW, it would be really, really nice if the code you supplied could actually be run. There are many errors with it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 21, 2010 at 9:41 pm
This is incomplete, and not quite right, but hopefully it will fire off someone's neurons to get it finished. I remember seeing something here (article or thread) dealing with just this, but I can't find it.
SELECT 'entry\@emp_cd' = RTRIM(emp_cd),
'entry\@last_name' = RTRIM(last_name),
'entry\@user_name' = RTRIM(user_name),
'entry\@hiredate' = hiredate,
'entry\@birthdate' = birthdate,
'position\@code' = RTRIM(position_code),
'position\@primary' = [primary],
'position\@org_cd' = RTRIM(org_cd),
'position\@org_level_cd' = RTRIM(org_level_cd),
'position\@eff_date' = eff_date
FROM employee
FOR XML RAW,ROOT('empload');
I can't find the right combination of which XML node to use (AUTO, RAW, PATH, EXPLICIT) and which directives (ROOT, TYPE) to use, but I'm 99% sure that the column names are being formed correctly. The caveat is that all of the "entry", "position", etc must be done together.
Oh... here is the supplied test code, modified to be able to actually run:
if object_id('tempdb..#employee','U') IS NOT NULL DROP TABLE #employee;
Create table #employee
(emp_cd char(16) NOT NULL
, last_name char(64) NULL
, first_name char(32) NULL
, middle_name char(32) NULL
, address1 char(40) NULL
, address2 char(40) NULL
, city char(48) NULL
, [state] char(16) NULL
, zip char(15) NULL
, country char(32) NULL
, user_name char(16) NULL
, hiredate char(10) NULL
, birthdate char(10) NULL
, email char(128) NULL
, position_code char(12) NOT NULL
, [primary] char(1) NOT NULL
, org_cd char(12) NOT NULL
, org_level_cd char(12) NOT NULL
, eff_date char(10) NOT NULL
, status_code char(12) NOT NULL
, status_eff_date char(10) NOT NULL
, payrate_value decimal(16,6) NOT NULL
, unit_cd char(12) NOT NULL
, type_cd char(12) NOT NULL
, payrate_eff_date char(10) NOT NULL
, schedplan_code char(12) NOT NULL
, sched_eff_date char(10) NOT NULL
, payplan_code char(12) NOT NULL
, payplan_eff_date char(10) NOT NULL)
Insert into #employee (emp_cd, last_name, first_name, middle_name, address1, address2, city,[state], zip, country, user_name, hiredate, birthdate, email
, position_code, [primary], org_cd, org_level_cd, eff_date
, status_code, status_eff_date
, payrate_value, unit_cd, type_cd, payrate_eff_date
, schedplan_code, sched_eff_date
, payplan_code, payplan_eff_date)
Values
(1010, 'Passela', 'Lucia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,4400,'01/01/2010','01/01/1980',NULL,
1010,'Y',5156,'STORE','01/01/2010',
'Active','01/01/2010',
20.5,'HOUR','BASE','01/01/2010',
'NYFTH','01/01/2010',
'NYFTH','01/01/2010'),
(50006,'Maloney','Ellen',NULL,NULL,NULL,NULL,NULL,NULL,NULL,560506,'01/01/2010','01/01/1980',NULL,
1000,'Y',5156,'STORE','01/01/2010',
'ACTIVE','01/01/2010',
16.16,'HOUR','BASE','01/01/2010',
'NYFTH','01/01/2010',
'NYFTH','01/01/2010'),
(564449,'Collins','Laura',NULL,NULL,NULL,NULL,NULL,NULL,NULL,565449,'01/01/2010','01/01/1980',NULL,
1090,'Y',5156,'STORE','01/01/2010',
'ACTIVE','01/01/2010',
21.9,'HOUR','BASE','01/01/2010',
'NYFTH','01/01/2010',
'NYFTH','01/01/2010');
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 22, 2010 at 11:27 am
Thank you so much Wayne.
I tried your solution, but I am not getting the below 3 lines of code in my output:
<?xml version = "1.0" encoding="UTF-8" ?>
<!DOCTYPE empload (View Source for full doctype...)>
<empload global_version="1.0">
I am seeing the results in SQL Management Studio.
December 22, 2010 at 12:42 pm
I'll have a look at it within the next hour. Promise.
December 22, 2010 at 1:22 pm
lsurapaneni (12/22/2010)
Thank you so much Wayne.I tried your solution, but I am not getting the below 3 lines of code in my output:
<?xml version = "1.0" encoding="UTF-8" ?>
<!DOCTYPE empload (View Source for full doctype...)>
<empload global_version="1.0">
I am seeing the results in SQL Management Studio.
More likely than not, it's because the DTD declaration isn't valied. the parentheses in there don't pass muster. The statement you approximated in isn't valid. Make the DTD valid, and you should be able to see the XML.
Your best bet at that point is to hardcode those in upfront.
look at xml.com for valid DTD declaration samples.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2010 at 2:06 pm
Here's a version that will provide the structure as requested based on Waynes sample data and sample code (Thank you, Wayne!!).
However, due to the DTD declaration you won't be able to view the data as xml data type within SSMS.
Therefore, I decided to leave the result as VARCHAR(MAX). As soon as your leading comments allow to be converted into valid xml (as per SQL Server....), you should be able to convert it to xml data type (if needed at all...).
SELECT
'<?xml version = "1.0" encoding="UTF-8" ?>
<!DOCTYPE empload (View Source for full doctype...)>
<empload global_version="1.0">'
+(
SELECT '@emp_cd' = RTRIM(emp_cd),
'@last_name' = RTRIM(last_name),
'@user_name' = RTRIM(USER_NAME),
'@hiredate' = hiredate,
'@birthdate' = birthdate,
'position/@code' = RTRIM(position_code),
'position/@primary' = [PRIMARY],
'position/@org_cd' = RTRIM(org_cd),
'position/@org_level_cd' = RTRIM(org_level_cd),
'position/@eff_date' = eff_date
FROM employee
FOR XML PATH ('entry'),ROOT('empload')
)
+'</empload>';
December 22, 2010 at 2:16 pm
Lutz just brought up a good point: if this is your presentation format, do NOT convert it back to the XML data type (it will strip out the XML declaration at very least).
SQL will reformat the XML as needed so that it can interact with it as data, so it will remove those declarations (they're optional)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2010 at 6:19 pm
Thank you very much Wayne and Lutz.
I will try this solution tomorrow and hopefully it will work
December 22, 2010 at 6:20 pm
LutzM (12/22/2010)
Here's a version that will provide the structure as requested based on Waynes sample data and sample code (Thank you, Wayne!!).
You're welcome.
However, this misses the specs every-so-slightly. What your code is producing is:
<empload global_version="1.0">
<empload>
<entry ...>
</entry>
</empload>
<empload>
<entry ...>
</entry>
</empload>
...
</empload>
The <empload> / </empload> tags shouldn't be there (except for the final </empload>)
I'm struggling with this... this is new to me, and I'm trying to learn it also. (I might be able to shred XML, and build simple XML, but not this slightly more complex stuff. Good learning opportunity!)
@Lutz: This might make for a good SQL Spackle article! Go for it!
Edit: This is the code that I currently have. I think that it's producing the correct results. Man, what a learning experience! :w00t:
SELECT
'<?xml version = "1.0" encoding="UTF-8" ?>' +
'<!DOCTYPE empload (View Source for full doctype...)>' +
'<empload global_version="1.0">' +
(SELECT '@emp_cd' = RTRIM(emp_cd),
'@last_name' = RTRIM(last_name),
'@user_name' = RTRIM(USER_NAME),
'@hiredate' = hiredate,
'@birthdate' = birthdate,
'position/@code' = RTRIM(position_code),
'position/@primary' = [PRIMARY],
'position/@org_cd' = RTRIM(org_cd),
'position/@org_level_cd' = RTRIM(org_level_cd),
'position/@eff_date' = eff_date,
'status/@code' = RTRIM(status_code),
'status/@eff_date' = status_eff_date,
'payrate/@value' = payrate_value,
'payrate/@unit_cd' = RTRIM(unit_cd),
'payrate/@type_cd' = RTRIM(type_cd),
'payrate/@eff_date' = payrate_eff_date,
'schedplan/@code' = RTRIM(schedplan_code),
'schedplan/@eff_date' = sched_eff_date,
'payplan/@code' = RTRIM(payplan_code),
'payplan/@eff_date' = payplan_eff_date
FROM #employee
FOR XML PATH ('entry'))
+ '</empload>';
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 23, 2010 at 2:50 am
Hi Wayne,
the reason for the additional empload tag is the "ROOT('empload')" code snippet.
The ROOT element basically is the "wrapper" around the xml code since a wellfomrmed xml needs a covering tag.
And you figured it absolutely right: when I added the empload tag with the global_version attribute I should have removed the ROOT from the FOR XML clause.
Good catch & Well done!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply