Início / dba / Perguntas / 126724
Accepted
Kaishu
Asked: 2016-01-21 03:34:22 +0800 CST

Como ler vários registros do xml para inserir na tabela no 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

Estou tentando obter o resultado do procedimento armazenado no formato xml e, lendo esse resultado, armazená-lo na tabela temporária criada acima. Mas estou obtendo resultado como nullprocedimento armazenado atual retornando apenas seis registros em xml, mas mostrando 36 linhas na tabela temporária com nullvalores nela. Como consertar isso?

Este é o formato xml:

formato xml

sql-server sql-server-2012

1 respostas

  1. Best Answer
    1. Você precisa remover o /no início da value()função. ou seja, este Listing.value('(/.
    2. Você também precisa remover o CROSS JOINque ocorre porque você está usando a JOINsintaxe de estilo antigo entre ListingTable(Listing)e TempTable(ReportData). Você pode fazer isso referenciando o PCMReportData de maneira ligeiramente diferente.

    Com base no seu esquema XML, você deve obter valores, por exemplo

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

    Você está fazendo um CROSS JOINentre ListingTable(Listing)e TempTable(ReportData). Portanto, os 6 resultados, multiplicados pelos 6 resultados internos = 36.

    • 3

relate perguntas