DECLARE @tbl_XMLResult table
    Status varchar(50),
    Address varchar(100),
    ListPrice varchar(25),
    SoldPrice varchar(25),
    YearBuilt varchar(25),
    SF varchar(25),
    Acres varchar(25),
    OnMarkDate varchar(25),
    PendDate varchar(25),
    SaleDate varchar(25),
    DOM varchar(25)


exec usp_ReportResults @query = "759,905,1048,170,725,80129", @ReportName = "GenReport", @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult(Status, Address, ListPrice, SoldPrice,
        YearBuilt, SF, Acres, OnMarkDate, PendDate, SaleDate, DOM)
        Listing.value('(/ListingStatus/text())[1]', 'varchar(50)') AS Status, 
        ReportData.value('(/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address,
        Listing.value('(/ListPrice/text())[1]', 'varchar(50)') AS ListPrice,
        Listing.value('(/ClosePrice/text())[1]', 'varchar(50)')AS SoldPrice,
        Listing.value('(/YearBuilt/text())[1]', 'varchar(50)') AS YearBuilt,
        Listing.value('(/TotalLvSpace/text())[1]', 'varchar(50)') AS SF,
        Listing.value('(/AcresApx/text())[1]', 'varchar(50)') AS Acres,
        Listing.value('(/ListDate/text())[1]', 'varchar(50)') OnMarkDate,
        Listing.value('(/PendingDate/text())[1]', 'varchar(50)') AS PendDate,
        Listing.value('(/CloseDate/text())[1]', 'varchar(50)') AS SaleDate,
        Listing.value('(/DaysOnMarket/text())[1]', 'varchar(50)') DOM
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

FROM @tbl_XMLResult

I am trying to get result from stored procedure as in xml format and by reading that result store it into the temporary table created above. But I'm getting result as null and current stored procedure returning only six records in xml but it showing 36 rows in temporary table with null values in it. How to fix that?

This is the xml format:

xml format


1 Answer 1

  1. You need to remove the / at the start of the value() function. i.e. this one Listing.value('(/.
  2. 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.


SET @XMLRESULT = '<Results ReplyCode="0" ReplyText="Operation Successfull">
                    <Report PropertyType="ResidentialProperty">
                        <Listing_StreetAddress>My Street Address</Listing_StreetAddress>

  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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.