Convert XML to SQL Server table

Question

I've seen a freight service but I can't convert the returned XML into a table.

I was trying to use this example on SQL Server 2016 but for some reason it is not working, what I'm doing wrong?

I got the XML from a query on this web service:

SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.asmx/CalcPrecoPrazo?'+
    'nCdEmpresa='+@nCdEmpresa+
    '&sDsSenha='+@sDsSenha+
    '&sCepOrigem='+@sCepOrigem+
    '&sCepDestino='+@sCepDestino+
    '&nVlPeso='+@nVlPeso+
    '&nCdFormato='+CONVERT(VARCHAR(20), @nCdFormato)+
    '&nVlComprimento='+CONVERT(VARCHAR(20), @nVlComprimento)+
    '&nVlAltura='+CONVERT(VARCHAR(20), @nVlAltura)+
    '&nVlLargura='+CONVERT(VARCHAR(20), @nVlLargura)+
    '&sCdMaoPropria='+@sCdMaoPropria+
    '&nVlValorDeclarado='+CONVERT(VARCHAR(20), @nVlValorDeclarado)+
    '&sCdAvisoRecebimento='+@sCdAvisoRecebimento+
    '&nCdServico='+@nCdServico+
    '&nVlDiametro='+CONVERT(VARCHAR(20), @nVlDiametro)+
    '&StrRetorno=xml';

    EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUTPUT;
    EXEC sp_OAMethod @Obj, 'open', NULL, 'GET', @Url, FALSE;
    EXEC sp_OAMethod @Obj, 'send';
    EXEC sp_OAGetProperty @Obj, 'responseText', @Response OUTPUT;
    EXEC sp_OADestroy @Obj;

    SET @XML = @Response COLLATE SQL_Latin1_General_CP1251_CS_AS

Then I tried to do this but it is not working:

--Result from frist code
DECLARE @XML XML = '<cResultado xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/">
  <Servicos>
    <cServico>
      <Codigo>4014</Codigo>
      <Valor>17,20</Valor>
      <PrazoEntrega>3</PrazoEntrega>
      <ValorMaoPropria>0,00</ValorMaoPropria>
      <ValorAvisoRecebimento>0,00</ValorAvisoRecebimento>
      <ValorValorDeclarado>0,00</ValorValorDeclarado>
      <EntregaDomiciliar>S</EntregaDomiciliar>
      <EntregaSabado>S</EntregaSabado>
      <Erro>0</Erro>
      <MsgErro />
      <ValorSemAdicionais>17,20</ValorSemAdicionais>
      <obsFim />
    </cServico>
  </Servicos>
</cResultado>';

DECLARE @XmlHandle INT,
        @PrepareXmlStatus INT;

EXEC @PrepareXmlStatus= sp_xml_preparedocument @XmlHandle OUTPUT, @XML  


SELECT  *
FROM    OPENXML(@XmlHandle, '/cResultado/Servicos/cServico', 2)  
    WITH (
    Codigo INT,
    Valor SMALLMONEY,
    PrazoEntrega INT,
    ValorMaoPropria SMALLMONEY,
    ValorAvisoRecebimento SMALLMONEY,
    ValorValorDeclarado SMALLMONEY,
    EntregaDomiciliar VARCHAR(1),
    EntregaSabado VARCHAR(1),
    Erro INT,
    MsgErro VARCHAR(500),
    ValorSemAdicionais SMALLMONEY,
    obsFim VARCHAR(500)
    )  


EXEC sp_xml_removedocument @XmlHandle

See the result

score 1 · Accepted Answer · answered Dec 12 '17 at 18:01

I would recommend to use the built-in XQuery support in SQL Server instead of the old, rather clunky OPENXML code.

Also, you are not respecting the default XML namespace that is defined on your XML document:

<cResultado .....   xmlns="http://tempuri.org/">
                    ***************************

You need to take that into account!

Try this code here (and you can extend it to return all the individual bits and pieces of your <cServico> XML node):

WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/')
SELECT
    Codigo = XC.value('(Codigo)[1]', 'int'),
    Valor = XC.value('(Valor)[1]', 'varchar(20)'),
    PrazoEntrega = XC.value('(PrazoEntrega)[1]', 'int')
    -- add more expressions like the above for the other sub-elements
FROM
    @XML.nodes('/cResultado/Servicos/cServico') AS XT(XC)

Convert XML to SQL Server table

1 Answers1