none
Query SQL em Campo XML RRS feed

  • Pergunta

  •  

    Olá amigos, eu tenho um componente em minha aplicação .net que grava a seguinte estrutura (xml) em uma coluna em minha tabela. Algo semelhante a isso:

    <RecurrenceInfo Start="04/12/2011 08:00:00" End="04/14/2011 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" />

    Preciso realizar uma consulta entre os atributos "Start" e "End", como fosse um comando Between!

    Se alguem puder ajudar, agradeço desde já!

     

    []d

    • Movido Gustavo Maia Aguiar terça-feira, 12 de abril de 2011 18:22 (De:SQL Server - Desenvolvimento Geral)
    terça-feira, 12 de abril de 2011 17:29

Respostas

  • Boa Tarde,

    Segue um exemplo:

    declare @xml xml
    set @xml = '<RecurrenceInfo Start="04/12/2011 08:00:00" End="04/14/2011 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" />'
    declare @datainicial datetime, @datafinal datetime
    set @datainicial = @xml.value('(/RecurrenceInfo/@Start)[1]','datetime')
    set @datafinal = @xml.value('(/RecurrenceInfo/@End)[1]','datetime')
    select @datainicial, @datafinal
    -- select <alguns campos> from <algumas tabelas> where <algumas condições> and data between @datainicial and @datafinal
    

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 12 de abril de 2011 18:22
    • Marcado como Resposta Aline Bossi quarta-feira, 13 de abril de 2011 11:57
    terça-feira, 12 de abril de 2011 18:22

Todas as Respostas

  • Boa Tarde,

    Segue um exemplo:

    declare @xml xml
    set @xml = '<RecurrenceInfo Start="04/12/2011 08:00:00" End="04/14/2011 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" />'
    declare @datainicial datetime, @datafinal datetime
    set @datainicial = @xml.value('(/RecurrenceInfo/@Start)[1]','datetime')
    set @datafinal = @xml.value('(/RecurrenceInfo/@End)[1]','datetime')
    select @datainicial, @datafinal
    -- select <alguns campos> from <algumas tabelas> where <algumas condições> and data between @datainicial and @datafinal
    

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 12 de abril de 2011 18:22
    • Marcado como Resposta Aline Bossi quarta-feira, 13 de abril de 2011 11:57
    terça-feira, 12 de abril de 2011 18:22
  • Olá Gustavo, realmente estas instruções funcionaram perfeitamente aqui, minha dúvida agora é: Como colocarei o campo que eu preciso na variável @xml??? Explicando melhor, minha tabela, resumidamente, tem a seguinte estrutura: (Id, Subject, RecurrenceInfo), na coluna RecurrenceInfo é que está o <RecurrenceInfo Start="04/12/2011 08:00:00" End="04/14/2011 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" />.

    Como farei para que em cada linha da minha tabela eu verifique se um parâmetro data está entre as datas Start e End???

    Desde já muito obrigada

    []s


    • Marcado como Resposta Aline Bossi quarta-feira, 13 de abril de 2011 12:29
    • Não Marcado como Resposta Gustavo Maia Aguiar quarta-feira, 13 de abril de 2011 14:59
    quarta-feira, 13 de abril de 2011 12:03
  • Bom Dia,

    Já tenho algumas idéias em mente, mas ajudaria se você tivesse algum exemplo com os dados e o resultado esperado. Você poderia postar o CREATE TABLE com alguns INSERTs e o resultado esperado ?

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com 


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 13 de abril de 2011 14:59
  • Gustavo o CREATE da minha tabela é a seguinte:

    CREATE

    TABLE [dbo].[Appointments]

     

    [UniqueId] [int]

    IDENTITY(1,1) NOT NULL,
     

    [Type] [int]

    NULL,
     

    [StartDate] [datetime]

    NULL,
     

    [EndDate] [datetime]

    NULL,

    [AllDay] [bit]

    NULL,

    [Subject] [nvarchar]

    (100) NULL,

    [Location] [nvarchar]

    (50) NULL,

    [Description] [ntext]

    NULL,

    [Status] [int]

    NULL,

    [Label] [int]

    NULL,

    [ResourceID] [int]

    NULL CONSTRAINT [DF_Appointments_ResourceID] DEFAULT ((0)),

    [RecurrenceInfo] [ntext]

    NULL,

    [RemiderInfo] [ntext]

    NULL,

    [IdVereador] [int]

    NULL,

     

    CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED

    (

    [UniqueId]

    ASC

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Um exempro de dados que posso te dar é esse aqui:

     

    336 1 12/04/2011 08:00:00 12/04/2011 08:30:00 False evento evento NULL 0 0 NULL <RecurrenceInfo Start="04/12/2011 08:00:00" End="04/14/2011 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" /> NULL 28

     

    Tenho uma agenda e quando um evento é marcado para vários dias é preenchido a coluna RecurrenceInfo, preciso exibir isso em um outro aplicativo mas quando o dia atual está somente marcado no campo em xml não consigo selecionar o evento.

     

     

    Espero ter explicado um pouco melhor agora

    Muito obrigada

     

    quarta-feira, 13 de abril de 2011 16:38
  • Bom Dia Aline,

    Desculpe a demora, mas vamos lá.

    Há dois pontos que merecem atenção no seu código. O primeiro ponto é que a coluna RecurrenceInfo é uma coluna TEXT e não XML. Você terá que convertê-la sempre que quiser consultar o XML e isso incorre em penalidades de desempenho, pois, se a coluna não é XML não podemos criar índices XML sobre ela, mas enfim... O outro ponto é que você está trabalhando com dois formatos de data. Nas colunas StartDate e EndDate você usa o formato brasileiro, mas no XML você usa o formato americano e isso pode lhe complicar mais na frente.

    Segue uma possível solução ao que acho que é o seu problema.

    CREATE TABLE [dbo].[Appointments] (
     [UniqueId] [int] IDENTITY(1,1) NOT NULL, 
     [Type] [int] NULL, 
     [StartDate] [datetime] NULL, 
     [EndDate] [datetime] NULL, 
     [AllDay] [bit] NULL, 
     [Subject] [nvarchar](100) NULL, 
     [Location] [nvarchar](50) NULL, 
     [Description] [ntext] NULL, 
     [Status] [int] NULL, 
     [Label] [int] NULL, 
     [ResourceID] [int] NULL CONSTRAINT [DF_Appointments_ResourceID] DEFAULT ((0)), 
     [RecurrenceInfo] [ntext] NULL, 
     [RemiderInfo] [ntext] NULL, 
     [IdVereador] [int] NULL, 
    CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED ([UniqueId] ASC))
    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    INSERT INTO Appointments (
     Type, StartDate, EndDate,
     AllDay,Subject,Location,Description,Status,Label,ResourceID,
     RecurrenceInfo)
     VALUES (
     1, '20110412 08:00:00','20110412 08:30:00',
     0,'evento','evento',NULL,0,0,NULL,
     '<RecurrenceInfo Start="04/12/2011 08:00:00" End="20110414 08:00:00" Id="32004086-970f-4dd0-9be3-bf351c2c8354" OccurrenceCount="3" Range="1" />')
    ;With CXML As (
    SELECT *, CAST(RecurrenceInfo As XML) As RecurrenceInfoXML
    FROM Appointments)
    SELECT
     Type, StartDate, EndDate,
     X.x.value('(/RecurrenceInfo/@Start)[1]','datetime') As StartFromXML,
     X.x.value('(/RecurrenceInfo/@End)[1]','datetime') As EndFromXML
    FROM CXML As C
    CROSS APPLY RecurrenceInfoXML.nodes('.') As X(x)
    

    Se houvesse uma coluna XML poderíamos usá-la sem a necessidade da CTE com o WITH

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com 


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 21 de abril de 2011 13:26