Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
query to load data in the xml format

Answered query to load data in the xml format

  • Wednesday, February 13, 2013 1:06 AM
     
     
    Sign In to Vote
    0

    I have data as below in the table:

    userid    empid      location

    1           121          TX

    1           121          FL

    1           121          NY

    2           135          NJ

    3           145          AZ

    I want a query to generate the above data as follows as I need to load the data into the table in the below format:

    userid        empid             location

    1                121               <loc>TX</loc><loc>FL</loc><loc>NY</loc>

    2                135               <loc>NJ</loc>

    3                145               <loc>AZ</loc>

    Thanks in advance




    • Edited by guest369 Wednesday, February 13, 2013 1:10 AM
    •  
     

All Replies

  • Wednesday, February 13, 2013 1:32 AM
     
     Answered Has Code
    Sign In to Vote
    0

    One method:

    WITH locations AS (
	SELECT 
		userid
		, empid
	FROM dbo.MyTable
	GROUP BY 
		userid
		, empid
	)
SELECT
	userid
	, empid
	, (SELECT location AS loc 
		FROM dbo.MyTable AS b 
		WHERE
			locations.userid = b.userid
			AND locations.empid = b.empid
		FOR XML PATH('')) AS location
FROM locations;

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com