As Jeff points out, the xml isn't ideal which is true, but I have come up with something that makes a lot of assumptions!. Mainly around the ordering of the xml which if changes, will break this so I give no assurances to this code 😀

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)

Returns:

emp_idemp_nameskills_idskill

1Bob1tsql

1Bob2SSRS

1Bob3SSAS

1Bob4SSIS

1Bob5Replication

2Frank1tsql

2Frank2SSRS

2Frank3SSAS