title: Convert XML to SQL Server table
body: <p>I've seen a freight service but I can't convert the returned XML into a table.</p>
  
  <p>I was trying to use <a href="https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server">this example</a> on SQL Server 2016 but for some reason it is not working, what I'm doing wrong?</p>
  
  <p>I got the XML from a query on this web service:</p>
  
  <pre><code>SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.asmx/CalcPrecoPrazo?'+
      'nCdEmpresa='+@nCdEmpresa+
      '&amp;sDsSenha='+@sDsSenha+
      '&amp;sCepOrigem='+@sCepOrigem+
      '&amp;sCepDestino='+@sCepDestino+
      '&amp;nVlPeso='+@nVlPeso+
      '&amp;nCdFormato='+CONVERT(VARCHAR(20), @nCdFormato)+
      '&amp;nVlComprimento='+CONVERT(VARCHAR(20), @nVlComprimento)+
      '&amp;nVlAltura='+CONVERT(VARCHAR(20), @nVlAltura)+
      '&amp;nVlLargura='+CONVERT(VARCHAR(20), @nVlLargura)+
      '&amp;sCdMaoPropria='+@sCdMaoPropria+
      '&amp;nVlValorDeclarado='+CONVERT(VARCHAR(20), @nVlValorDeclarado)+
      '&amp;sCdAvisoRecebimento='+@sCdAvisoRecebimento+
      '&amp;nCdServico='+@nCdServico+
      '&amp;nVlDiametro='+CONVERT(VARCHAR(20), @nVlDiametro)+
      '&amp;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
  </code></pre>
  
  <p>Then I tried to do this but it is not working:</p>
  
  <pre><code>--Result from frist code
  DECLARE @XML XML = '&lt;cResultado xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/"&gt;
    &lt;Servicos&gt;
      &lt;cServico&gt;
        &lt;Codigo&gt;4014&lt;/Codigo&gt;
        &lt;Valor&gt;17,20&lt;/Valor&gt;
        &lt;PrazoEntrega&gt;3&lt;/PrazoEntrega&gt;
        &lt;ValorMaoPropria&gt;0,00&lt;/ValorMaoPropria&gt;
        &lt;ValorAvisoRecebimento&gt;0,00&lt;/ValorAvisoRecebimento&gt;
        &lt;ValorValorDeclarado&gt;0,00&lt;/ValorValorDeclarado&gt;
        &lt;EntregaDomiciliar&gt;S&lt;/EntregaDomiciliar&gt;
        &lt;EntregaSabado&gt;S&lt;/EntregaSabado&gt;
        &lt;Erro&gt;0&lt;/Erro&gt;
        &lt;MsgErro /&gt;
        &lt;ValorSemAdicionais&gt;17,20&lt;/ValorSemAdicionais&gt;
        &lt;obsFim /&gt;
      &lt;/cServico&gt;
    &lt;/Servicos&gt;
  &lt;/cResultado&gt;';
  
  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
  </code></pre>
  
  <p><a href="https://www2.baraodemaua.br/images/tsql_error_read_xml.png" rel="nofollow noreferrer">See the result</a></p>