Filtrar coluna XML no SQL Server

Para filtrar conteúdos

Gerando conteúdo

DECLARE @XmlData XML
SET @XmlData = N'<TransactionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <BasketHash>437e04ac543d10433e518a56246ad384</BasketHash>
  <Billing>
    <CustormerName>Fabian Corrêa Marques</CustormerName>
    <Email>test@mailinator.com</Email>
    <CelPhone>9999999999</CelPhone>
    <Login>test@mailinator.com</Login>
    <Identity Type="CPF">123456789</Identity>
    <BirthDate>1986-04-17T00:00:00</BirthDate>
    <Gender>M</Gender>
    <AddressLine>Rua Imaginária</AddressLine>
    <Number>123</Number>
    <Complement>Apartamento 103</Complement>
    <Neighbourhood>Centro</Neighbourhood>
    <City>Porto Alegre</City>
    <State>RS</State>
    <Country>BRA</Country>
    <PostalCode>123456789</PostalCode>
    <Phone>5555555555</Phone>
  </Billing>
  <Shipping>
    <CustormerName>Fabian Corrêa Marques</CustormerName>
    <Email>test@mailinator.com</Email>
    <CelPhone>9999999999</CelPhone>
    <Login>test@mailinator.com</Login>
    <Identity Type="CPF">123456789</Identity>
    <BirthDate>1986-04-17T00:00:00</BirthDate>
    <Gender>M</Gender>
    <AddressLine>Rua Imaginária</AddressLine>
    <Number>123</Number>
    <Complement>Apartamento 103</Complement>
    <Neighbourhood>Centro</Neighbourhood>
    <City>Porto Alegre</City>
    <State>RS</State>
    <Country>BRA</Country>
    <PostalCode>123456789</PostalCode>
    <Phone>5555555555</Phone>
  </Shipping>
  <Customer>
    <CreatedDate>2016-07-26T17:24:09.24</CreatedDate>
  </Customer>
  <WebSite>
    <WebSiteID>1</WebSiteID>
    <WebSiteUrl>https://www.correamarques.com.br/</WebSiteUrl>
  </WebSite>
  <Request>
    <UserAgent>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36</UserAgent>
  </Request>
  <PayableOrders>
    <OrderInfo>
      <OrderID>e09ccef8-f543-4eba-9679-9e31a7426e87</OrderID>
      <CustomerID>694013</CustomerID>
      <OrderNumber>CM-694013-00002</OrderNumber>
      <OrderDate>2017-03-27T14:39:02.4292-03:00</OrderDate>
      <Items>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985936</Identifier>
          <Description>Whey Protein Concentrado (1KG)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">85.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985819</Identifier>
          <Description>BCAA (120 caps)</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">25.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>987772</Identifier>
          <Description>Albumina 500gr</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">33.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985925</Identifier>
          <Description>100% Batata Doce em pó (sabor natural) (1kg)</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">44.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985899</Identifier>
          <Description>Coqueteleira 3 compartimentos (com MixBall)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">22.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985851</Identifier>
          <Description>Multivitaminico (120 caps) (nova fórmula)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">33.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Delivery</Type>
          <Identifier>2</Identifier>
          <Description>PAC</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">23.34</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Discount</Type>
          <Identifier>76</Identifier>
          <Description>Desconto de 2,00%</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">6.880</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Discount</Type>
          <Identifier>2</Identifier>
          <Description>Desconto na entrega</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">23.34</UnitPrice>
        </ItemInfo>
      </Items>
      <ShopperTicketID>e8f0765b-a7fd-42ac-a5bd-b5125684d582</ShopperTicketID>
      <Total>337.12</Total>
      <PrepaidAmount>0</PrepaidAmount>
      <Properties>
        <PropertyInfo>
          <Name>utm_source</Name>
          <Value>buscape</Value>
        </PropertyInfo>
        <PropertyInfo>
          <Name>utm_medium</Name>
          <Value>cpc</Value>
        </PropertyInfo>
        <PropertyInfo>
          <Name>utm_campaign</Name>
          <Value>lowerfunnel</Value>
        </PropertyInfo>
      </Properties>
      <Seller>
        <SellerID>0</SellerID>
        <BankAgencyDigit xsi:nil="true" />
        <BankAccountDigit xsi:nil="true" />
        <CommissionAmount>0</CommissionAmount>
        <IsPercentageCommission>false</IsPercentageCommission>
      </Seller>
    </OrderInfo>
  </PayableOrders>
</TransactionInfo>'

Obter o valor da coluna do XML

SELECT @XmlData.value('(/TransactionInfo/PayableOrders/OrderInfo/Total)[1]','decimal(10,2)') AS PaypalTotalPaid

Fontes:
XQuery for the SQL Server DBA: XPath
http://www.allaboutmssql.com/2012/09/xqueryxpathxmlschemaxml-index_6.html
http://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper/22711296

Procurar conteúdo dentro de uma view / procedure do SQL Server

Pesquisar conteúdo dentro da view

Select smv.definition
FROM sys.all_views AS v
JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
where smv.definition like '%product%'

Pesquisar conteúdo dentro da procedure

select smv.definition
from sys.procedures as p
JOIN sys.sql_modules AS smv ON smv.object_id = p.object_id
where smv.definition like '%product%'

Pesquisar conteúdo dentro de uma trigger

select * from sys.triggers t
JOIN sys.sql_modules AS smv ON smv.object_id = t.object_id
where smv.definition like '%product_parents_hierarchy%'

Executar o mesmo script em várias bases no SQL Server

Hoje precisei pesquisar um determinado registro em várias no SQL Server mas não queria ter que ir no SQL Management Studio base a base para executar o script, até porque em algumas bases pode não existir as tabelas necessárias para executar o meu script.

Para facilitar esse processo repetitivo existe a proc sp_MSforeachdb que executa o script em todas as bases.

DECLARE @Command VARCHAR(1000)
SELECT @Command = 'USE [?];
IF COL_LENGTH(''[dbo].[products]'', ''Id'') IS not NULL
	ALTER TABLE dbo.products ADD description varchar(5000)'

EXEC sp_MSforeachdb @Command

Descrevendo o trecho de código veremos que primeiramente iremos declarar uma variável para armazenar o comando a ser executado:
DECLARE @Command VARCHAR(1000)

Logo após iremos armazenar o script que desejamos executar na variável.

SELECT @Command = 'USE [?];
IF COL_LENGTH(''[dbo].[products]'', ''Id'') IS not NULL
	ALTER TABLE dbo.products ADD description varchar(5000)'

E por fim executamos o script no banco.
EXEC sp_MSforeachdb @Command

Filtrar registros

Muitas vezes necessito trazer no resultado do select apenas as databases que possuem um determinado registro e para isso utilizo o script abaixo.

DECLARE @Schema VARCHAR(20) = 'dbo'
DECLARE @Table VARCHAR(80) = 'configurations'
DECLARE @ColumnToFilter VARCHAR(80) = 'FullName'
DECLARE @Where VARCHAR(200) = @ColumnToFilter + ' = ''General.Caching.RedisHosts'' AND Value like ''%hlg%'''

-- Não alterar os campos abaixo
DECLARE @SchemaAndTable VARCHAR(80) = '[' + @Schema + '].[' + @Table + ']'
DECLARE @Command VARCHAR(1000)

SELECT @Command = 'USE [?];
IF	EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE table_name = ''' + @Table + ''') 
	AND COL_LENGTH(''' + @SchemaAndTable + ''', ''' + @ColumnToFilter + ''') IS not NULL
	BEGIN
		IF EXISTS ( SELECT 1 FROM ' + @SchemaAndTable + ' WHERE ' + @Where +')
			BEGIN
				SELECT ''?'' as dbname, T.Value FROM ' + @SchemaAndTable + ' AS T WHERE ' + @Where +'
			END
	END
'
EXEC sp_MSforeachdb @Command

Pesquisar procedure que não contém determinado campo

DECLARE @Procedure VARCHAR(80) = 'pBasket'
DECLARE @Column VARCHAR(200) = 'skuname'
DECLARE @Command VARCHAR(1000)

SELECT @Command = 'USE [?];
IF NOT EXISTS(	SELECT 1 FROM sys.procedures p
			JOIN sys.sql_modules AS smv ON smv.object_id = p.object_id
			WHERE p.name = ''' + @Procedure + ''' 
			AND definition like ''%' + @Column + '%''
	)
	BEGIN
		SELECT ''?'' as dbname
	END
'
EXEC sp_MSforeachdb @Command

Atualização (19/10/2016) – Filtrar registros
Atualização (10/11/2016) – Pesquisar procedure que não contém determinado campo

LinqToSQL converter objeto para ToString

Hoje estava trabalhando no projeto da empresa onde eu precisava mostrar na tela os dados da fatura de utilização do sistema.
O problema: No banco de dados o período da fatura está em uma campo do tipo datetime, i.e. armazena a data e hora, mas para mostrar na tela eu preciso apenas mostrar a data no formato mês/ano e como fazer para retornar do banco esse formato?

Como utilizamos o Entity Framework para fazer o acesso aos dados, utilizamos os recursos do LinqToSQL.

Continue lendo “LinqToSQL converter objeto para ToString”

Script para remover todas as tabelas do SQL Server

Quando estamos em desenvolvendo um projeto grandes, e.g. ERP precisamos muitas vezes testar script de criação/atualização do base de dados para poder entregar ao nosso cliente.

Para resolver esse problema poderia ser excluído o banco de dados e depois recriado, mas para isso precisamos mexer com a questão de permissão de acesso ao banco e muitas vezes não temos essa permissão no servidor do cliente, por esse motivo através de muita pesquisa fiz o comando abaixo para excluir todas as tabelas e restrições do banco de dados onde está sendo executado o comando.

Continue lendo “Script para remover todas as tabelas do SQL Server”

Verificar a existência de uma coluna no SQL Server

Microsoft SQL Server

Um dos grandes problemas para atualizar uma base de dados através de forma automatizada é com o problema dos campos na base de dados, no SQL Server provemos do utilitário SQL Command que provê acesso ao ao SGDB via linha de comando e nos auxiliar a realizar essa tarefa.

Nos scripts de atualização sempre faço a verificação da existência do campo na base de dados antes de criar o campo, abaixo temos um exemplo de como verificar a existência do campo através de uma consulta a tabela do Sql Server que armazena todas as colunas existentes:

IF NOT EXISTS(SELECT * FROM sys.columns 
              WHERE [name] = N'nomeDaColuna' AND [object_id] = OBJECT_ID(N'nomeDaTabela'))
BEGIN
    ALTER TABLE nomeDaTabela ADD nomeDaColuna INT NOT NULL DEFAULT 0;
END

Outro forma de verificar a existência da coluna é através do comando COL_LENGTH que retorna a quantidade de bytes que o campo utiliza para armazenar os dados.

IF COL_LENGTH('nomeDaTabela', 'nomeDaColuna') IS NULL
BEGIN
     ALTER TABLE nomeDaTabela ADD nomeDaColuna INT NOT NULL DEFAULT 0;
END

Caso a coluna pelo qual foi executada a pesquisa for do tipo VARCHAR(MAX) o resultado do COL_LENGTH será -1.
Observação: Retorna NULL em caso de erro ou se o usuário que estiver executando a query não tiver permissão para exibir o objeto.

Caso desejar adicionar uma descrição na coluna recentemente criada poderemos executar a proc abaixo:

EXEC sp_addextendedproperty 
@name = N'Description', @value = 'Longa descrição da coluna',
@level0type = N'Schema', @level0name = Schema,
@level1type = N'Table',  @level1name = Tabela,
@level2type = N'Column', @level2name = Coluna;
GO