>Thanks Naomi and Sergey for the answer
>
>I agree with the need to optimize the query but I don´t know how. First of all the error occurs when I run from the application not from SSMS. From the last the query is fast.
>
>The idea is:
>
>I have a table named VendasOrcamento with a XML field that stores the items of the sales
>
>The query retrives the items in a relational format, joining with tables product and category to get some information about the product.
>The final format of data is

>prodname refeetiq eancode localizador numeracao posicao ProdID qtd valorU grade
>SOBRETUDO 7520 9990001006600 230 P/GG 2 100660 1 30.30 P/0 M/01 G/0 GG/0
>
>Any help will be appreciated
>
>
>

>
>XML Sample
><OrcamentoItens>
>  <Produto>
>    <ProdID>101154</ProdID>
>    <Qtd>3</Qtd>
>    <ValorU>11.80</ValorU>
>    <Obs />
>    <Grade>P/0 M/2 G/1</Grade>
>  </Produto>
>  <Produto>
>    <ProdID>100402</ProdID>
>    <Qtd>8</Qtd>
>    <ValorU>8.90</ValorU>
>    <Obs />
>    <Grade>P/3 M/3 G/2</Grade>
>  </Produto>
>
>Query
>DECLARE @xmlItens XML
>SET  @xmlItens=(select itens.query('/OrcamentoItens') from dbo.VendasOrcamento where id=@tcOrcamentoID)
>
>
>
>
>SELECT prodname,refeetiq,eancode,localizador,numeracao,posicao,
> OrcamentoItens.Produtos.value('ProdID[1]','char(10)')
>  As ProdID,
> OrcamentoItens.Produtos.value('Qtd[1]','int') as qtd,
> OrcamentoItens.Produtos.value('ValorU[1]','numeric(10,2)') as valorU,
> OrcamentoItens.Produtos.value('Grade[1]','varchar(60)') as grade,
> OrcamentoItens.Produtos.value('Obs[1]','varchar(254)') as obs,
> @tcOrcamentoID as orcamentoID,
> @dadosCliente as ClienteInfo,
> @InternetID as InternetID
>FROM
> @xmlItens.nodes('/OrcamentoItens/Produto') OrcamentoItens(Produtos)
>left join produto on produto.id=OrcamentoItens.Produtos.value('ProdID[1]','char(10)')
>inner join categprod on produto.categoryID=categprod.ID
>
>

I think rather than joining directly based on nodes and value you need to sort of pre-process XML to turn it into the table using CROSS APPLY rather than join.

I'm not that good with XML manipulation of SQL Server, but check this blog http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html and the article at the end of this blog.