DECLARE

    @message xml

    SET @message=

    '<root>

  <table_name>JobPieces</table_name>

  <trigger_name>TR_async_trigger_JobPieces</trigger_name>

  <user_name>sa</user_name>

  <inserted>

<inserted JobID="1234591" PieceID="3" PieceStatus="1" LastChanged="2020-08-28T14:50:22" />

<inserted JobID="1234591" PieceID="2" PieceStatus="1" LastChanged="2020-08-28T14:50:22" />

<inserted JobID="1234591" PieceID="1" PieceStatus="1" LastChanged="2020-08-28T14:50:22" />

  </inserted>

</root>'

SELECT

    JobID = T.c.value('@JobID[1]', 'int'),

    PieceID = T.c.value('@PieceID[1]', 'int'),

    PieceStatus = T.c.value('@PieceStatus[1]', 'int'),

    [Timestamp] = T.c.value('@LastChanged[1]', 'datetime')

FROM @message.nodes('/root/inserted/inserted') T(c)