Dúvida Usando XQuery

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.