In the future try to include the actual XML (or table data) instead of a screenshot as it's easier for us to provide a good answer. Also, try to explain where the XML (or data) is coming from; this information is also helpful for providing a better answer. That said, here's how you would do this against a table or a variable. Note my comments:

-----------------------------------------------------------------------------------------

-- if the xml is coming in as a variable or parameter

-----------------------------------------------------------------------------------------

-- sample XML

DECLARE @xml XML =

'<consumerTransactionList>

<consumerTransaction>

<sourceSystem>

<id>1</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

<consumerTransaction>

<sourceSystem>

<id>2</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

</consumerTransactionList>';

-- solution

SELECT

CAST

('<consumerTransactionList>'+

CAST(nd.r.query('.') AS varchar(8000))+

'</consumerTransactionList>'

AS XML)

FROM (VALUES (@xml)) doc(r)

CROSS APPLY r.nodes('/consumerTransactionList/consumerTransaction') nd(r);

-----------------------------------------------------------------------------------------

-- if the xml is coming from a table

-----------------------------------------------------------------------------------------

-- create and populate the sample table

IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable;

SELECT *

INTO #SomeTable

FROM

(

SELECT id = 100, SomeXML = CAST

('<consumerTransactionList>

<consumerTransaction>

<sourceSystem>

<id>1</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

<consumerTransaction>

<sourceSystem>

<id>2</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

</consumerTransactionList>' AS xml)

UNION ALL

SELECT id = 101, CAST

('<consumerTransactionList>

<consumerTransaction>

<sourceSystem>

<id>3</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

<consumerTransaction>

<sourceSystem>

<id>4</id>

<moreStuff>blah blah blah</moreStuff>

</sourceSystem>

</consumerTransaction>

</consumerTransactionList>' AS xml)

) x

GO

SELECT

id,

CAST

('<consumerTransactionList>'+

CAST(nd.r.query('.') AS varchar(8000))+

'</consumerTransactionList>'

AS XML)

FROM #SomeTable st

CROSS APPLY st.SomeXML.nodes('/consumerTransactionList/consumerTransaction') nd(r);

DROP TABLE #SomeTable;

A good links for learning XML: http://www.w3schools.com/xml/[/url]

An important understanding how my solution works:

https://msdn.microsoft.com/en-us/library/ms191474.aspx

A good SQL XML link: https://msdn.microsoft.com/en-us/library/ms187339.aspx