SQL Server Select in XML field I need a help with the situation below:

In my table SQLServer 2012 have a field with xml values, I would like to make a select of the data in that field and show the result in the form columns.



&lt;row&gt; 
  &lt;ID_Cota&gt;162986&lt;/ID_Cota&gt;
  &lt;ID_Taxa_Plano&gt;1000&lt;/ID_Taxa_Plano&gt;
  &lt;ID_Plano_Venda&gt;1020&lt;/ID_Plano_Venda&gt;
  &lt;ID_Pessoa&gt;18522&lt;/ID_Pessoa&gt;
&lt;/row&gt;


Thank you.
Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this:

IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable
GO
CREATE TABLE dbo.yourTable ( 
    rowId INT IDENTITY PRIMARY KEY, 
    ST_Registro_Del XML
)
GO

INSERT INTO dbo.yourTable ( ST_Registro_Del )
SELECT '&lt;row&gt; 
  &lt;ID_Cota&gt;162986&lt;/ID_Cota&gt;
  &lt;ID_Taxa_Plano&gt;1000&lt;/ID_Taxa_Plano&gt;
  &lt;ID_Plano_Venda&gt;1020&lt;/ID_Plano_Venda&gt;
  &lt;ID_Pessoa&gt;18522&lt;/ID_Pessoa&gt;
&lt;/row&gt;'
GO


SELECT 
    r.c.value('(ID_Cota/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Taxa_Plano/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Plano_Venda/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Pessoa/text())[1]', 'INT' ) AS ID_Cota
FROM dbo.yourTable yt
    CROSS APPLY yt.ST_Registro_Del.nodes('row') r(c)
WHERE rowId = 1


Here are my results:



The .nodes method returns a resultset from XML.  Use the text() accessor for a performance boost when querying untyped XML ( ie no XSD is associated with the XML column and the [1] ordinal, which is a shorthand way of saying "get me the first element you find".

HTH