How to Create XML Array in SQL

Asked

Viewed 17 times

0

I have the following XML structure

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:gen="http://www.rapidaocometa.com.br/GenPoWMS10In" xmlns:mesa="http://www.sterlingcommerce.com/mesa">
   <soapenv:Header />
   <soapenv:Body>
      <mesa:mesaAuth>
         <mesa:principal>ws_webphones_test</mesa:principal>
         <mesa:auth hashType="?">5vf@xE4</mesa:auth>
      </mesa:mesaAuth>
      <InstancePo xmlns="http://www.rapidaocometa.com.br/GenPoWMS10In">
         <PO>
            <HEADER>
              <ORDERID>123</ORDERID>
            </HEADER>
            <LINES>
               <SKU>18243</SKU>
               <SKUDESCRIPTION />
               <NIR_CFOP>0</NIR_CFOP>
            </LINES>
            <LINES>
               <SKU>18243</SKU>
               <SKUDESCRIPTION />
               <NIR_CFOP>0</NIR_CFOP>
            </LINES>             
         </PO>
      </InstancePo>
   </soapenv:Body>
</soapenv:Envelope>```

Tenho que abrir e fechar o <LINE> de acordo com a quantidade de itens que tiverem no meu pedido.

Hoje eu utilizo a função FOR XML PATH, para montar um array em JSON, no exemplo do código abaixo.

(SELECT REPLACE('[' +
                     ( SELECT

                     '{"sku_principal":"'+RTRIM(CONVERT(VARCHAR(100),ITE2.CODPROD))+'",'+

                     '"sku_produto":"'+RTRIM(CONVERT(VARCHAR(100),ITE2.CODPROD))+'",'+

                     '"nome_item":"'+RTRIM(CONVERT(VARCHAR(100),PRO.DESCRPROD))+'",'+

                    '"valor_item":"'+CONVERT(VARCHAR(100),(ISNULL(((ITE2.QTDNEG*ITE2.VLRUNIT)-ITE2.VLRDESC-ITE2.VLRREPRED+ITE2.VLRSUBST+ITE2.VLRIPI+C.VLROUTROS),0)/ITE2.QTDNEG))+'",'+

                    '"desconto":"'+RTRIM(CONVERT(VARCHAR(100),ITE2.VLRDESC))+'",'+

                    '"quantidade":"'+RTRIM(CONVERT(VARCHAR(100),ITE2.QTDNEG))+'"}',','

                    FROM SANKHYA.TGFITE ITE2 (NOLOCK)
                    LEFT JOIN SANKHYA.TGFPRO PRO (NOLOCK) ON PRO.CODPROD = ITE2.CODPROD

                    WHERE ITE2.NUNOTA = C.NUNOTA                                             
                            
                    FOR XML PATH ('') ) +']',',]',']')) AS itens

** Existe alguma forma de realizar o mesmo procedimento, só que para estrutura em XML ?**
sql sql-server

1 answer

0


I was able to solve the problem using FOR PATH XML. The code was in the format below.

SELECT(SELECT 
    ITE.CODPROD AS SKU,  
    ITE.VLRUNIT AS NIR_PRECO_UNITARIO1   
 FROM SANKHYA.TGFCAB CAB  (NOLOCK)
 LEFT JOIN SANKHYA.TGFITE  (NOLOCK) ITE  ON ITE.NUNOTA = CAB.NUNOTA WHERE CAB.NUNOTA = 4985488 AND CAB.CODEMP = 103 FOR XML PATH ('LINES')) AS ITENS;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.