'<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_name>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_name>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_name>SSAS</skills_id>
	<skills_id>4</skills_id>
	<skills_name>SSIS</skills_id>
	<skills_id>5</skills_id>
	<skills_name>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_name>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_name>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_name>SSAS</skills_id>
</skills>
</employee>
</employees>
'

DECLARE @xml XML
SET @xml = '<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_id>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_id>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_id>SSAS</skills_id>
	<skills_id>4</skills_id>
	<skills_id>SSIS</skills_id>
	<skills_id>5</skills_id>
	<skills_id>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_id>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_id>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_id>SSAS</skills_id>
</skills>
</employee>
</employees>
'


SELECT e.c.value('(emp_id)[1]', 'int') AS emp_id
	, e.c.value('(emp_name/text())[1]', 'varchar(50)') AS emp_name
	, s.c.value('(.)[1]', 'varchar(50)') AS skills_id
FROM @xml.nodes('/employees/employee') e(c)
CROSS APPLY e.c.nodes('skills/skills_id') AS s(c)

DECLARE @xml XML
SET @xml = '<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_id>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_id>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_id>SSAS</skills_id>
	<skills_id>4</skills_id>
	<skills_id>SSIS</skills_id>
	<skills_id>5</skills_id>
	<skills_id>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
	<skills_id>1</skills_id>
	<skills_id>tsql</skills_id>
	<skills_id>2</skills_id>
	<skills_id>SSRS</skills_id>
	<skills_id>3</skills_id>
	<skills_id>SSAS</skills_id>
</skills>
</employee>
</employees>
'

SELECT @xml = @xml.query('
<employees>
{
	for $x in //employee 
		return 
			<employee emp_name="{$x/emp_name/text()}" emp_id="{$x/emp_id/text()}">
				{
					for $y in $x/skills/skills_id[number(text()[1]) > 0]
					return 
						<skill id="{data($y)}" value="{data($x/skills/skills_id[. >> $y][1])}"/>
				}
			</employee>
}
</employees>
')

SELECT e.c.value('@emp_id', 'int') AS emp_id
	, e.c.value('@emp_name', 'varchar(50)') AS emp_name
	, s.c.value('@id', 'int') AS skills_id
	, s.c.value('@value', 'varchar(50)') AS skill
FROM @xml.nodes('/employees/employee') e(c)
CROSS APPLY e.c.nodes('skill') AS s(c)

emp_id	emp_name	skills_id	skill
1	Bob	1	tsql
1	Bob	2	SSRS
1	Bob	3	SSAS
1	Bob	4	SSIS
1	Bob	5	Replication
2	Frank	1	tsql
2	Frank	2	SSRS
2	Frank	3	SSAS