none
Dúvida Usando XQuery RRS feed

  • Pergunta

  • Pessoal,

    Eu tenho uma tabela em que uma das colunas é do tipo ntext em que os dados são guardados em formato xml.
    Bom, não sei o motivo do desenvolvedor não ter usado o tipo xml para a coluna.

    Eu tenho o seguinte dado guardado na coluna.

    <?xml version="1.0" encoding="utf-8" ?>
     <UnitInformation>
     <section name="Segment List">
      <setting name="Count" value="0" />
      </section>
      <section name="Test Log">
      <setting name="OS" value="XP" />
      <setting name="Retries0" value="0" />
      <setting name="PrtCommXRev" value="0.17.1.0" />
      <setting name="FirmwareCode" value="20070627" />
      </section>
      <section name="Canon PA">
      <setting name="PrinterName" value="testeee" />
      <setting name="ProductSerialNum" value="teste2" />
      <setting name="DefPaperSize" value="Letter" />
      <setting name="MACAddress" value="000000000000" />
      <setting name="FirmwareCode" value="20070627 3D17" />
      <setting name="MaxPrintResolution" value="FastRes 1200" />
      <setting name="ServiceID" value="0000000" />
      <setting name="PageCount" value="0" />
      </section>
     </UnitInformation>


    Eu gostaria de buscar o valor para o FirmwareCode dentro do Section "Test Log"


    Eu fiz a seguinte query



    declare @XML as xml
    declare @xmlString as varchar(max)
    select top 1 @xmlString=OutFile from tabela
    set @xmlString = replace(@xmlString,'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>','')
    set @XML = cast(@xmlString as xml)
    select @XML.value('(/UnitInformation/section/setting[@name="FirmwareCode"])[1]','varchar(50)')

    Porém não me volta nada...

    Também tentei partir para algo parecido com
    SELECT T.c.query('setting') as Valor
    FROM   @XML.nodes('/UnitInformation/section') T(c)


    Porém também não consigo retornar o que eu quero.

    Alguém pode me ajudar com isso???

    Obrigado,

    Cassiano


    segunda-feira, 22 de dezembro de 2008 19:27

Respostas

  • Olá Cassiano,

     

    Seja bem vindo ao fórum de SQL Server do MSDN.

     

    Nós profissionais do fórum, tentaremos ajudá-lo com suas dúvidas e problemas a cerca do SQL Server através de nosso conhecimento, experiência e disponibilidade.

     

    Eu particularmente gosto muito do tema XML e fico surpreso quando aparecem dúvidas de XML no fórum (é bem incomum). Acredito que embora tenha um razoável conhecimento de XPath e XQuery, há algo estranho quando existe um atributo chamado value. Consegui resolver embora não seja uma solução perfeita.

     

    Code Snippet

    DECLARE @T TABLE (ColunaTexto VARCHAR(MAX))

     

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20070627" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

     

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20080530" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

     

    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT CAST(ColunaXML.query('for $U in (/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]) return data($U/@value)') AS CHAR(8))

    FROM Res

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 22 de dezembro de 2008 20:03
  • Olá Cassiano,

     

    É possível usando o método exists, mas para ser performático, pode ser necessário criar um Xml Index. Você pode considerar usar o IsNull contra o resultado final também. Segue a sintaxe baseada em XML.

     

    Code Snippet

    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT ColunaXML.value('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]','varchar(10)') FROM Res

    WHERE ColunaXML.exist('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]')=1

     

     

    Adicionalmente recomendaria a leitura do seguinte artigo:

     

    SQL Server 2005 New Features: Os métodos XML - Parte 1

    http://www.plugmasters.com.br/sys/materias/773/1/SQL-Server-2005-New-Features%3A-Os-m%E9todos-XML---Parte-1

     

    [ ]s,

     

    Gustavo

    terça-feira, 23 de dezembro de 2008 13:02

Todas as Respostas

  • Olá Cassiano,

     

    Seja bem vindo ao fórum de SQL Server do MSDN.

     

    Nós profissionais do fórum, tentaremos ajudá-lo com suas dúvidas e problemas a cerca do SQL Server através de nosso conhecimento, experiência e disponibilidade.

     

    Eu particularmente gosto muito do tema XML e fico surpreso quando aparecem dúvidas de XML no fórum (é bem incomum). Acredito que embora tenha um razoável conhecimento de XPath e XQuery, há algo estranho quando existe um atributo chamado value. Consegui resolver embora não seja uma solução perfeita.

     

    Code Snippet

    DECLARE @T TABLE (ColunaTexto VARCHAR(MAX))

     

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20070627" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

     

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20080530" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

     

    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT CAST(ColunaXML.query('for $U in (/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]) return data($U/@value)') AS CHAR(8))

    FROM Res

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 22 de dezembro de 2008 20:03
  • Olá Gustavo,

    Muito Obrigado pela resposta.

    Funcionou certinho sim.

    Não sei por que, você fez dois inserts em @T.
    Fazendo somente um, como é meu caso, deu tudo certo.

    Muito Obrigado pela ajuda.


    att,


    Cassiano Sanches
    segunda-feira, 22 de dezembro de 2008 20:32
  • Olá Cassiano,

     

    Fiz dois inserts, para simular dois registros. Assim a abordagem funciona com mais de um XML. Se você utilizasse uma variável, provavelmente funcionaria também, mas transpor de múltiplos registros para uma variável é fácil. O contrário nem sempre é verdadeiro.

     

    Achei a combinação certa. De fato a XQuery impõe algumas conversões já que estamos falando de recuperação de valores e não de nós. Com o Value fica mais "correto".

     

    Code Snippet

    DECLARE @T TABLE (ColunaTexto VARCHAR(MAX))

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20070627" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>

    <section name="Segment List">

    <setting name="Count" value="0" />

    </section>

    <section name="Test Log">

    <setting name="OS" value="XP" />

    <setting name="Retries0" value="0" />

    <setting name="PrtCommXRev" value="0.17.1.0" />

    <setting name="FirmwareCode" value="20080530" />

    </section>

    <section name="Canon PA">

    <setting name="PrinterName" value="testeee" />

    <setting name="ProductSerialNum" value="teste2" />

    <setting name="DefPaperSize" value="Letter" />

    <setting name="MACAddress" value="000000000000" />

    <setting name="FirmwareCode" value="20070627 3D17" />

    <setting name="MaxPrintResolution" value="FastRes 1200" />

    <setting name="ServiceID" value="0000000" />

    <setting name="PageCount" value="0" />

    </section>

    </UnitInformation>')

    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT ColunaXML.value('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]','smalldatetime') FROM Res

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 22 de dezembro de 2008 20:53
  • Entendi Gustavo.

    Muito Obrigado, você não sabe como isso vai me ajudar agora.


    Para funcionar do meu lado eu fiz o seguinte:

    DECLARE @T TABLE (ColunaTexto VARCHAR(MAX))

    INSERT INTO @T
    SELECT RunGtOutFile from mesarc_unitfalcontest where unit_ic = 'BRBS8DK0KD'
    ; WITH Res (RunGtOutFile) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)
    SELECT RunGtOutFile.value(
    '(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]','varchar(10)'
    ) as Firmware FROM Res



    Isso me retorna duas linhas, uma com o conteúdo e outra NULL.
    Tem como eu filtrar para não me retornar NULL?

    Obrigado novamente,

    Cassiano Sanches




    terça-feira, 23 de dezembro de 2008 10:24
  • Olá Cassiano,

     

    Você poderia postar o XML ?

    A solução da tabela foi para exemplificar, mas se a idéia é retornar apenas um único valor podemos trabalhar com variáveis.

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 23 de dezembro de 2008 10:58
  • Gustavo,

    Para simular eu peguei o código que você postou e no primeiro insert eu tirei o firmware do testlog


    DECLARE @T TABLE (ColunaTexto VARCHAR(MAX))

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>
    <section name="Segment List">
    <setting name="Count" value="0" />
    </section>
    <section name="Test Log">
    <setting name="OS" value="XP" />
    <setting name="Retries0" value="0" />
    <setting name="PrtCommXRev" value="0.17.1.0" />
    </section>
    <section name="Canon PA">
    <setting name="PrinterName" value="testeee" />
    <setting name="ProductSerialNum" value="teste2" />
    <setting name="DefPaperSize" value="Letter" />
    <setting name="MACAddress" value="000000000000" />
    <setting name="FirmwareCode" value="20070627 3D17" />
    <setting name="MaxPrintResolution" value="FastRes 1200" />
    <setting name="ServiceID" value="0000000" />
    <setting name="PageCount" value="0" />
    </section>
    </UnitInformation>')

    INSERT INTO @T VALUES ('<?xml version="1.0" encoding="utf-8" ?>

    <UnitInformation>
    <section name="Segment List">
    <setting name="Count" value="0" />
    </section>
    <section name="Test Log">
    <setting name="OS" value="XP" />
    <setting name="Retries0" value="0" />
    <setting name="PrtCommXRev" value="0.17.1.0" />
    <setting name="FirmwareCode" value="20080530" />
    </section>
    <section name="Canon PA">
    <setting name="PrinterName" value="testeee" />
    <setting name="ProductSerialNum" value="teste2" />
    <setting name="DefPaperSize" value="Letter" />
    <setting name="MACAddress" value="000000000000" />
    <setting name="FirmwareCode" value="20070627 3D17" />
    <setting name="MaxPrintResolution" value="FastRes 1200" />
    <setting name="ServiceID" value="0000000" />
    <setting name="PageCount" value="0" />
    </section>
    </UnitInformation>')
    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT ColunaXML.value('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]','varchar(10)') FROM Res
    terça-feira, 23 de dezembro de 2008 11:20
  • Olá Cassiano,

     

    Não há nada errado na sua instrução. A XPath simplesmente não retorna resultados para o primeiro registro. Veja que na sua estrutura XML não há FirmwareCode dentre de Test Log. É semelhante a uma cláusula WHERE que não retorna registros.

     

    [ ]s,

     

    Gustavo

    terça-feira, 23 de dezembro de 2008 12:30
  • Olá Gustavo,

    Eu gostaria que ele não me retornasse o NULL.
    Algo como "and Firmware is not null", porém usando xquery.

    Obrigado,

    Cassiano
    terça-feira, 23 de dezembro de 2008 12:34
  • Olá Cassiano,

     

    É possível usando o método exists, mas para ser performático, pode ser necessário criar um Xml Index. Você pode considerar usar o IsNull contra o resultado final também. Segue a sintaxe baseada em XML.

     

    Code Snippet

    ; WITH Res (ColunaXml) AS (SELECT CAST(ColunaTexto AS XML) FROM @T)

    SELECT ColunaXML.value('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]','varchar(10)') FROM Res

    WHERE ColunaXML.exist('(/UnitInformation/section[@name="Test Log"]/setting[@name="FirmwareCode"]/@value)[1]')=1

     

     

    Adicionalmente recomendaria a leitura do seguinte artigo:

     

    SQL Server 2005 New Features: Os métodos XML - Parte 1

    http://www.plugmasters.com.br/sys/materias/773/1/SQL-Server-2005-New-Features%3A-Os-m%E9todos-XML---Parte-1

     

    [ ]s,

     

    Gustavo

    terça-feira, 23 de dezembro de 2008 13:02
  • Perfeito Gustavo.

    Muito Obrigado.
    Ontem eu lí o seu artigo, porém ainda estava meio perdido para fazer o uso de XQuery.

    Agora vou tentar me virar sozinho para fazer joins usando conteúdo de dados em xml.

    Novamente muito obrigado.
    terça-feira, 23 de dezembro de 2008 13:12