SQL Server 2022

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],
  c.value('(value[position() = sql:column("pos.WorkOrder" )]/text())[1]', 'VARCHAR(20)') AS WorkOrder
FROM (
    SELECT
      Task       = md.md.value('let $i := item[@name = "Task"       ][1] return count(./item[. << $i]) + 1', 'int'),
      TaskNumber = md.md.value('let $i := item[@name = "Task Number"][1] return count(./item[. << $i]) + 1', 'int'),
      [Group]    = md.md.value('let $i := item[@name = "Group"      ][1] return count(./item[. << $i]) + 1', 'int'),
      WorkOrder  = md.md.value('let $i := item[@name = "Work Order" ][1] return count(./item[. << $i]) + 1', 'int')
    FROM @xml.nodes('dataset/metadata') md(md)
) pos
CROSS APPLY @xml.nodes('/dataset/data/row') AS t(c);

Task	TaskNumber	Group	WorkOrder
3361B11	1	01	MS7579
3361B11	2	50	MS7579
3361B11	3	02	JA0520