Sql-server – SQL Server Select in XML field

sql serversql server 2014sql-server-2012xml

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.

Image1

<row> 
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>

Thank you.

Best Answer

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 '<row> 
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>'
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:

test 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

Related Question