declare @xd xml;
set @xd = N'<TSAMessage>
  <BusinessInterface>Test</BusinessInterface>
  <BusinessOperation>PSETestResultNotify</BusinessOperation>
  <MessageId>483d697f</MessageId>
  <InternalMessageId>5bbe381d-e978-4793-95a0-9df0bc51cbfc</InternalMessageId>
  <CorrelationId>483d697f</CorrelationId>
  <SenderId>TMII</SenderId>
  <GeneratedTimeStamp>2012-01-23T13:33:22.935+02:00</GeneratedTimeStamp>
  <Message>
    <PSETestResult>
      <Request>
        <TestRequestID>23073103</TestRequestID>
        <TestRequestStatus>COMPLETE</TestRequestStatus>
        <ReportedContext>
          <HostID>FAMC</HostID>
          <HostRef>Specto_15936580</HostRef>
        </ReportedContext>
        <TestRequestResult>
          <TestRequestID>23073103</TestRequestID>
          <TestResultCode>Worker</TestResultCode>
          <TestResultDate>2012-01-23T13:31:21.000+02:00</TestResultDate>
          <TestType>NFOPortal</TestType>
          <TestResultSpecialisation>00</TestResultSpecialisation>
          <Comment>XACT:Modem Syncing
XACT: Downlink Speed = 1023000bps
SHDSL:Modem Syncing
LINE: Test OK: Vercode 00</Comment>
        </TestRequestResult>
        <PercentageDegrade>0.12</PercentageDegrade>
        <LocalizationResult>
          <SegmentEntity>
            <EntityID>0235411658</EntityID>
            <NicotraAlarmInd>N</NicotraAlarmInd>
          </SegmentEntity>
          <TestResults>
            <TestSet>
              <Category>TM Summary</Category>
              <Result name="Line_Status" value="Vercode 00 - Test OK" />
              <Result name="TM_Reason" value="xAct syncing and Line test ok" />
              <Result name="xAct_Status" value="Modem Syncing" />
              <Result name="TM_Action" value="Confirm service is restored with Customer" />
              <Result name="TM_Status" value="Pass" />
            </TestSet>
            <TestSet>
              <Category>xAct Results</Category>
              <Result name="DSLAM_STATION_NAME" value="Prince Albert IMAX" />
              <Result name="ATUC_CURRENT_ATN" value="8.6" />
              <Result name="DSLAM_Port_Number" value="13" />
              <Result name="ATUR_CURRENT_SNR" value="36.6 - Pass" />
              <Result name="ATUC_ATTAINABLE_KBPS" value="21876" />
              <Result name="PROFILE_NFO" value="ADSLupto1024af01" />
              <Result name="DSLAM_Slot_Number" value="12" />
              <Result name="ATUC_CHAN_CURRENT_TX_RATE" value="1023" />
              <Result name="AdslAturCurrOutputPower" value="-22" />
              <Result name="hwPortOperStatus" value="Activated" />
              <Result name="ATUC_CURRENT_SNR" value="29" />
              <Result name="ATUR_CURRENT_ATN" value="21.1 - Pass" />
              <Result name="OperStatus" value="1" />
              <Result name="AdslModulationType" value="MultiMode" />
              <Result name="AdslAtucCurrOutputPower" value="35" />
              <Result name="ATUR_CHAN_CURRENT_TX_RATE" value="507" />
              <Result name="ATUR_ATTAINABLE_KBPS" value="1224" />
            </TestSet>
            <TestSet>
              <Category>Line Results</Category>
              <Result name="AC_Voltage.AB" value="0.00" />
              <Result name="LDT" value="N/A" />
              <Result name="AC_Voltage.AE" value="0.00" />
              <Result name="DC_Voltage.BE" value="0.00" />
              <Result name="TestType" value="LOOP" />
              <Result name="Dispatch1" value="Dispatch: NO DISPATCH" />
              <Result name="DC_Voltage.AE" value="0.00" />
              <Result name="CPE.AB" value="YES" />
              <Result name="Impedance.AB" value="12.8" />
              <Result name="Noise.Earth" value="48.7" />
              <Result name="Resistance.BE" value="9999" />
              <Result name="VerCode" value="00" />
              <Result name="VerificationMsg" value="TEST OK" />
              <Result name="DC_Voltage.AB" value="0.00" />
              <Result name="Resistance.AB" value="2284" />
              <Result name="AC_Voltage.BE" value="0.00" />
              <Result name="Noise.Metallic" value="11.9" />
              <Result name="NLT" value="PASS" />
              <Result name="Resistance.AE" value="9999" />
              <Result name="Length" value="1.11 KM" />
              <Result name="Termination" value="TERMINATED" />
              <Result name="Impedance.AE" value="4629" />
              <Result name="Impedance.BE" value="9999" />
              <Result name="Capacitance.AB" value="2.68" />
            </TestSet>
          </TestResults>
        </LocalizationResult>
      </Request>
    </PSETestResult>
  </Message>
</TSAMessage>'

declare @pvf nvarchar(max) ,@sql nvarchar(max);

set @pvf = stuff((
select ' ,[' + rs.value('@name[1]','sysname') + ']'
from @xd.nodes('//Result') R(RS)
for xml path('')),1,2,'');

set @sql = N'select * from (
select
  rc.value(''HostRef[1]'',''sysname'') as HostRef
 ,rs.value(''@name[1]'',''sysname'') as RName
 ,rs.value(''@value[1]'',''sysname'') as RValue
from @xdoc.nodes(''//ReportedContext'') X(RC)
outer apply @xdoc.nodes(''//Result'') R(RS)
) t
pivot (MIN(RValue) for RName in (' + @pvf + ')) p'

exec sp_executesql @sql ,N'@xdoc xml' ,@xdoc = @xd