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>';

DECLARE @cols nvarchar(max);

WITH XMLNAMESPACES(DEFAULT 'http://developer.cognos.com/schemas/xmldata/1/')
SELECT
  @cols = CAST(@xml.query('
  text {
    for $i in dataset/metadata/item
    return concat(
      ", c.value(''(value[",
      string(count(./item[. << $i]) + 1),
      "]/text())[1]'', ''VARCHAR(100)'') AS [",
      $i/@name,
      "]
 "
    )
  }
  ') AS nvarchar(max));

SET @cols = STUFF(@cols, 1, 2, N'');

DECLARE @sql nvarchar(max) = '
WITH XMLNAMESPACES(DEFAULT ''http://developer.cognos.com/schemas/xmldata/1/'')
SELECT
    ' + @cols + '
FROM @xml.nodes(''/dataset/data/row'') AS t(c);
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@xml xml',
  @xml = @xml;

Task	Task Number	Group	Work Order
3361B11	3361B11	3361B11	3361B11
3361B11	3361B11	3361B11	3361B11
3361B11	3361B11	3361B11	3361B11


WITH XMLNAMESPACES(DEFAULT 'http://developer.cognos.com/schemas/xmldata/1/')
SELECT
    c.value('(value[1]/text())[1]', 'VARCHAR(100)') AS [Task]
  , c.value('(value[1]/text())[1]', 'VARCHAR(100)') AS [Task Number]
  , c.value('(value[1]/text())[1]', 'VARCHAR(100)') AS [Group]
  , c.value('(value[1]/text())[1]', 'VARCHAR(100)') AS [Work Order]
 
FROM @xml.nodes('/dataset/data/row') AS t(c);