I am not getting the output in desired format.

I tried the solution that you provided. Do I need to do nested xml? to get the nodes as below:

...

Thank you very much Wayne and Lutz.

I will try this solution tomorrow and hopefully it will work

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

I am using SQL 2005

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