By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @xml XML =
'<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<metadata>
<item name="Task" type="xs:string" length="-1"/>
<item name="Task Number" type="xs:string" length="-1"/>
<item name="Group" type="xs:string" length="-1"/>
<item name="Work Order" type="xs:string" length="-1"/>
</metadata>
<data>
<row>
<value>3361B11</value>
<value>1</value>
<value>01</value>
<value>MS7579</value>
</row>
<row>
<value>3361B11</value>
<value>2</value>
<value>50</value>
<value>MS7579</value>
</row>
<row>
<value>3361B11</value>
<value>3</value>
<value>02</value>
<value>JA0520</value>
</row>
</data>
</dataset>';
WITH XMLNAMESPACES(DEFAULT 'http://developer.cognos.com/schemas/xmldata/1/')
SELECT
c.value('(value[position() = sql:column("pos.Task" )]/text())[1]', 'VARCHAR(20)') AS Task,
c.value('(value[position() = sql:column("pos.TaskNumber")]/text())[1]', 'VARCHAR(20)') AS TaskNumber,
c.value('(value[position() = sql:column("pos.Group" )]/text())[1]', 'VARCHAR(20)') AS [Group],
| Task | TaskNumber | Group | WorkOrder |
|---|---|---|---|
| 3361B11 | 1 | 01 | MS7579 |
| 3361B11 | 2 | 50 | MS7579 |
| 3361B11 | 3 | 02 | JA0520 |