Criar Schema para Procedure com FOR XML - WCF SQL

Com o novo (nem tão novo assim) adaptador WCF-SQL, criar os schemas das procedures ficou simples, não é preciso usar o FOR XML como antigamente.

Mas as vezes precisamos trazer resultados da query aninhados (subquerys), e o FOR XML ainda é util e necessário.

Então vamos pegar o exemplo de uma venda qualquer, assim teremos uma Nota Fiscal e seus itens. Segue uma figura:

Notem que o registro principal, que é a Nota Fiscal, se repete várias vezes, esse tipo de resultado dentro do BizTalk fica muito trabalhoso de fazer o mapeamento.

Então teriamos que ter algo mais parecido com a seguinte imagem:

Agora o registro principal só vemos uma única vez e temos uma subquery, já em XML, representando os Itens da Venda.

Como ficaria a procedure na integra:

   1:  CREATE PROCEDURE spPocSchemaForXml

   2:  AS

   3:  begin

   4:   

   5:  BEGIN TRAN;

   6:      

   7:      WITH xmlnamespaces(DEFAULT 'http://POC_SchemaForXml/spPocSchemaForXml') 

   8:      SELECT NF.NumNotaFiscal, NF.DataVenda, NF.CodCliente, NF.ValorTotal, 

   9:               (SELECT 

  10:                      P.Descricao,

  11:                      P.ValorUnitario,

  12:                      V.Quantidade,

  13:                      (V.Quantidade * P.ValorUnitario) AS ValorTotalItem

  14:              FROM tbVendas V 

  15:              INNER JOIN tbProdutos P ON V.CodItem = P.CodItem

  16:              WHERE NF.NumNotaFiscal = V.NumNotaFiscal

  17:              FOR XML PATH('ITENS'), TYPE)

  18:      FROM tbNotaFiscal NF

  19:      order by NumNotaFiscal

  20:      FOR XML PATH('Vendas'), TYPE

  21:   

  22:      COMMIT TRAN;

  23:   

  24:  end

E o resultado dessa procedure seria um xml:

   1:  <Vendas>

   2:    <NumNotaFiscal>123</NumNotaFiscal>

   3:    <DataVenda>2014-04-29T15:15:17.400</DataVenda>

   4:    <CodCliente>1</CodCliente>

   5:    <ValorTotal>172</ValorTotal>

   6:    <ITENS>

   7:      <Descricao>Mouse</Descricao>

   8:      <ValorUnitario>6</ValorUnitario>

   9:      <Quantidade>2</Quantidade>

  10:      <ValorTotalItem>12</ValorTotalItem>

  11:    </ITENS>

  12:    <ITENS>

  13:      <Descricao>Teclado</Descricao>

  14:      <ValorUnitario>30</ValorUnitario>

  15:      <Quantidade>1</Quantidade>

  16:      <ValorTotalItem>30</ValorTotalItem>

  17:    </ITENS>

  18:    <ITENS>

  19:      <Descricao>Memoria</Descricao>

  20:      <ValorUnitario>130</ValorUnitario>

  21:      <Quantidade>1</Quantidade>

  22:      <ValorTotalItem>130</ValorTotalItem>

  23:    </ITENS>

  24:  </Vendas>

  25:  <Vendas>

  26:    <NumNotaFiscal>456</NumNotaFiscal>

  27:    <DataVenda>2014-04-29T15:16:55.197</DataVenda>

  28:    <CodCliente>2</CodCliente>

  29:    <ValorTotal>594</ValorTotal>

  30:    <ITENS>

  31:      <Descricao>Monitor</Descricao>

  32:      <ValorUnitario>245</ValorUnitario>

  33:      <Quantidade>1</Quantidade>

  34:      <ValorTotalItem>245</ValorTotalItem>

  35:    </ITENS>

  36:    <ITENS>

  37:      <Descricao>HD Externo</Descricao>

  38:      <ValorUnitario>249</ValorUnitario>

  39:      <Quantidade>1</Quantidade>

  40:      <ValorTotalItem>249</ValorTotalItem>

  41:    </ITENS>

  42:    <ITENS>

  43:      <Descricao>Gabinete</Descricao>

  44:      <ValorUnitario>100</ValorUnitario>

  45:      <Quantidade>1</Quantidade>

  46:      <ValorTotalItem>100</ValorTotalItem>

  47:    </ITENS>

  48:  </Vendas>

Como puxar o schema dessa procedure no BizTalk para ser usado na Send Port:

1. Crie um Novo Projeto de Biztalk.
2. Clique com o botão direito na aplicação Add –> Add Generated Items.
3. Escolha a opção Consume Adapter Service e no botão Add.
4. No Wizard Consume Adapter Service, no combo Select a binding, escolha o sqlBinding.
5. Clique no botão Configure. Na aba URI Properties, coloque as informações do Banco de Dados.
6. Na aba Security, coloque as informações do usuário para se conectar no Banco.
7. Clique no Botão Ok e em Connect.
8. No combo Select Contract Type, deixa a opção Client (Outbound Operations).
9. Na Janela Select a Category, escolha a opção Procedures.
10. Clique em cima da procedure criada e no botão Add.

Agora é só clicar no botão OK e o nosso schema esta criado. Segue imagem:

Nesse schema que foi criado, só precisamos do Request, o node spPocSchemaForXmlResponse não será usado, então podemos apagar. Ficará assim:

Criando o schema a partir de um Arquivo XML Bem Formado.

Essa segunda parte serve também pra quem vai usar essa técnica na Receive Port.

1. Execute a procedure que foi criada.
2. Salve o Xml em um arquivo POC.xml
3. No Visual Studio, Clique com o botão direito na aplicação e em Add –> Add Generated Items.
4. Escolha a opção Generated Schema e no botão Add.
5. Em Document Type, deixe a opção Well-Formed XML e clique no Botão Browse.
6. Procure o arquivo gerado no passo 3.
7. Clique em Open e em OK.

O schema gerado deve ficar parecido com a imagem a seguir:

1. Clique com o botão direito no node , e em Insert Schema Node –> Child Record.
2. Esse schema será o nosso Response, então sugiro que coloquem o nome do node de Request+Response, no meu caso seria o node spPocSchemaForXmlResponse
3. Recorte o node Vendas e cole no node que acabou de ser criado, de uma forma que ele fique abaixo desse node:

1. Clique no node Vendas e altere sua propriedade Max Occurs para unbounded.

OBS: Caso alguns dos campos retornem vazios na execução da procedure, o campo não será criado no schema. Depois é só criar normalmente, respeitando a ordem em que eles aparecem no resultado.

Configurando a Send Port.

Com os novos adaptadores WCF, ao gerar um schema, também é criado um binding que nos ajuda a configurar a Receive ou Send Port.

Utilizando a técnica FOR XML temos que configurar as portas a moda antiga, ou seja, na munheca. :)

1. No Administration Console crie uma Send Port do tipo Request Response.
2. Escolha o Adaptador WCF-SQL e clique em Configure.
3. Na Guia General, seção Endpoint Address, clique em Configure e coloque as informações pertinentes ao banco.
4. No Action, digite:

XmlProcedure/dbo/spPocSchemaForXml.

1. Na aba bingind, na seção FOR XML, digite o node de response do nosso schema na propriedade XmlStoredProcedureRootNodeName.
2. Na propriedade XmlStoredProcedureRootNodeNameNamespace digite o namespace do schema criado.

Configurando a Receive Port.

1. Para usar um schema com FOR XML não é preciso seguir a primeira parte do post.
2. No Administration Console, crie uma Receive Port e Receive Location do tipo One-Way.
3. Escolha o Adaptador WCF-SQL e clique em Configure.
4. Na Guia General, seção Endpoint Address, clique em Configure e coloque as informações pertinentes ao banco.
5. Na aba bingind, na seção FOR XML, digite o node de request do schema na propriedade XmlStoredProcedureRootNodeName.
6. Na propriedade XmlStoredProcedureRootNodeNameNamespace digite o namespace do schema criado.
7. Na seção Inbound no combo InboundOperationType, escolha XmlPooling.
8. Na seção Pooling (Inbound), preencha as propriedades PooledDataAvailableStatement e PoolingStatement, com a query para verificar se existe dados disponiveis e com o nome da procedure respectivamente. 

Então é isso. Fácil ne?

Neste artigo vimos como criar um schema a partir de uma procedure com a clausula FOR XML.

Até a próxima. 

E voce? Já ajudou a comunidade hoje?