DECLARE @t TABLE (Part INT, Color VARCHAR(10), Size VARCHAR(10))

INSERT INTO @t
SELECT 123,'blue','small'
UNION ALL SELECT 124,'black','medium'
UNION ALL SELECT 125,'red','large'


select * from @t

DECLARE @xml VARCHAR(max)

SELECT @xml  = (
SELECT Part, Color, Size, '/' + cast(Part as varchar) AS PartC
FROM @t
FOR XML PATH(''), ROOT('PartsList') )

SELECT REPLACE(REPLACE(REPLACE(REPLACE( @xml,'</Part>','>'),'<Part>','<'),'</PartC>','>'),'<PartC>','<') 

declare @x xml
set @x='<PartsList>
 <a123>
 <Color>blue</Color>
 <Size>small</Size>
 </a123>
 <a124>
 <Color>black</Color>
 <Size>medium</Size>
 </a124>
 <a125>
 <Color>red</Color>
 <Size>large</Size>
 </a125>
</PartsList>'

select c.value('local-name(.)','varchar(100)') colname,
	c.value('(./Color)[1]','varchar(100)'),
	c.value('(./Size)[1]','varchar(100)') size
 from @x.nodes('/PartsList/*') vm(c)
		

declare @x xml

;with a as (
select 'a123' partID, 'blue' color,'large' size
union all
select 'a124' partID, 'green' color,'medium' size
union all
select 'a125' partID, 'red' color,'small' size)
select @x=(select partid as "@partid",color as "color", size from a for XML path ('part'), root('PartList'), type)

select cast(replace(replace(cast(
@x.query
	(' 
		for $a in PartList,
			$e in $a/part
		return <partlist>
			{concat("<",string($e/@partid),">")}
			{$e/*}
			{concat("</",string($e/@partid),">")}
		</partlist>
') as varchar(max)),'<','<'),'>','>') as XML)

<personGenderCodeCurrent>M</personGenderCodeCurrent>