主页 / dba / 问题 / 126724
Accepted
Kaishu
Asked: 2016-01-21 03:34:22 +0800 CST

如何从 xml 读取多条记录以插入 SQL 表中？

  • 772
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)
)

DECLARE @XMLRESULT xml

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)
    SELECT
        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
    FROM 
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

SELECT *
FROM @tbl_XMLResult

我正在尝试以 xml 格式从存储过程中获取结果，并通过读取该结果将其存储到上面创建的临时表中。但是我得到的结果是null当前存储过程在 xml 中仅返回六条记录，但它在临时表中显示了 36 行，其中包含null值。如何解决？

这是xml格式：

xml格式

sql-server sql-server-2012

1 个回答

  1. Best Answer
    1. 您需要在函数/开始时删除 。value()即这个Listing.value('(/。
    2. 您还需要删除CROSS JOIN发生的情况，因为您在andJOIN之间使用旧样式语法。您可以通过稍微不同的方式引用 PCMReportData 来做到这一点。ListingTable(Listing)TempTable(ReportData)

    然后根据您的 XML 模式，您应该获取值，例如

    DECLARE @XMLRESULT XML;

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

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);

    你正在做一个CROSS JOINbetween ListingTable(Listing)and TempTable(ReportData)。因此，6 个结果乘以 6 个内部结果 = 36。

    • 3

相关问题