How to read multiple record from xml to insert in table in SQL?
You need to remove the / at the start of the value() function. i.e. this one Listing.value('(/.
You also need to remove the CROSS JOIN that occurs because you are using old style JOIN syntax between ListingTable(Listing) and TempTable(ReportData). You can do that by referencing the PCMReportData slightly differently.


Based on your XML schema, you should then get values e.g.

DECLARE @XMLRESULT XML;

SET @XMLRESULT = '&lt;Results ReplyCode="0" ReplyText="Operation Successfull"&gt;
                    &lt;Report PropertyType="ResidentialProperty"&gt;
                    &lt;Listings&gt;
                    &lt;Listing&gt;
                      &lt;ListingID&gt;759&lt;/ListingID&gt;
                      &lt;PCMReportData&gt;
                        &lt;Listing_StreetAddress&gt;My Street Address&lt;/Listing_StreetAddress&gt;
                      &lt;/PCMReportData&gt;
                    &lt;/Listing&gt;
                    &lt;Listing&gt;
                      &lt;ListingID&gt;852&lt;/ListingID&gt;
                    &lt;/Listing&gt;
                    &lt;/Listings&gt;
                    &lt;/Report&gt;
                    &lt;/Results&gt;';

SELECT 
  Listing.value('(ListingID/text())[1]', 'varchar(50)') AS ListingID,
  Listing.value('(PCMReportData/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing);


You're doing a CROSS JOIN between ListingTable(Listing) and TempTable(ReportData). Therefore the 6 results, multiplied by the 6 inner results = 36.