It doesn't get dumped anywhere, it is just a select query.

To insert the transformed xml into a table, just add an insert statement before the select i.e.

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

INSERT INTO SomeTable (SomeXmlColumn)

SELECT @xml.query('

<Workers>

{

for $x in //d1p1:Worker

return

<Worker>

<EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

<FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

</Worker>

}

</Workers>

')