Fetching XML attribute of a node element as column and other node element as record value

As @Martin Smith pointed out, only dynamic SQL is capable to help here. Here is how to implement it.

SQL

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.tbl;

CREATE TABLE dbo.tbl (ID INT IDENTITY PRIMARY KEY, [Configuration] xml);
INSERT INTO dbo.tbl ([Configuration])
VALUES
(N'<Package>
   <Section name="BOX">
       <InputNumber description="[1] What is the height of the Box."> 
           <value>25</value>
       </InputNumber>
       <InputNumber description="[2] What is the width of the Box."> 
           <value>30</value>
       </InputNumber>
    </Section>
</Package>')
, (N'<Package>
   <Section name="BOX">
       <InputNumber description="[1] What is the height of the Box."> 
           <value>770</value>
       </InputNumber>
       <InputNumber description="[2] What is the width of the Box."> 
           <value>771</value>
       </InputNumber>
    </Section>
</Package>');
-- DDL and sample data population, end

DECLARE @xml XML
    , @colHeader VARCHAR(100)
    , @CrLf CHAR(2) = CHAR(13) + CHAR(10)
    , @SQL AS NVARCHAR(MAX);

SET @xml = (SELECT TOP(1) [Configuration] FROM dbo.tbl);

-- count total number of columns
DECLARE @cnt INT, @i INT;
SET @cnt = @xml.value('count(/Package/Section/InputNumber)', 'INT');

SET @SQL = 'SELECT ID' + @CrLf;

-- loop through XML
SET @i = 1;
WHILE @i <= @cnt BEGIN
   SET @colHeader = @xml.value('(/Package/Section/InputNumber[position() = sql:variable("@i")]/@description)[1]', 'VARCHAR(100)');
   SET @SQL += ', c.value(''(InputNumber[' + CAST(@i AS VARCHAR(5)) + ']/value/text())[1]'', ''NVARCHAR(MAX)'') AS "' + @colHeader + '"' + @CrLf

   SET @i += 1;
END

SET @SQL += 'FROM dbo.tbl as tbl
    CROSS APPLY tbl.[Configuration].nodes(''/Package/Section'') AS t(c);';

PRINT @SQL;
EXEC sys.sp_executesql @SQL;

Output

+----+------------------------------------+-----------------------------------+
| ID | [1] What is the height of the Box. | [2] What is the width of the Box. |
+----+------------------------------------+-----------------------------------+
|  1 |                                 25 |                                30 |
|  2 |                                770 |                               771 |
+----+------------------------------------+-----------------------------------+