Sqlserver Select in XML field

Question

I need some help with the situation below:

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

Image 1

<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.

Answer 1

Hey, Varaujo, I went around here and it worked.

if you need to apply some filter to your query, I advise you to turn the query suggested by Marconi into a CTE or create a View for it.

declare @tabela as table (linha xml);
insert into @tabela VALUES ('<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>');

insert into @tabela VALUES ('<row> 
  <ID_Cota>162987</ID_Cota>
  <ID_Taxa_Plano>999</ID_Taxa_Plano>
  <ID_Plano_Venda>1021</ID_Plano_Venda>
  <ID_Pessoa>18523</ID_Pessoa>
</row>');

WITH CTE_Tabela AS (
    select 
        linha.value('(/row/ID_Cota)[1]', 'int') AS ID_Cota,
        linha.value('(/row/ID_Taxa_Plano)[1]', 'int') AS ID_Taxa_Plano,
        linha.value('(/row/ID_Plano_Venda)[1]', 'int') AS ID_Plano_Venda,
        linha.value('(/row/ID_Pessoa)[1]', 'int') AS ID_Pessoa
    FROM @tabela
)
SELECT * FROM CTE_Tabela WHERE ID_Cota = 162987

If you want to create a Generic method to bring all the nodes of a given item, you can make a CROSS APPLY.

DECLARE @tabela as table (linha xml);
DECLARE @sql nvarchar(max);    
DECLARE @campos varchar(max);

insert into @tabela VALUES ('<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>');

insert into @tabela VALUES ('<row> 
  <ID_Cota>162987</ID_Cota>
  <ID_Taxa_Plano>999</ID_Taxa_Plano>
  <ID_Plano_Venda>1021</ID_Plano_Venda>
  <ID_Pessoa>18523</ID_Pessoa>
</row>');

SELECT 
    @campos = COALESCE(@campos + ', ', '') + Campo 
FROM (
    SELECT DISTINCT 'linha.value(''(/row/' + T2.loc.value('local-name(.)', 'varchar(20)') + ')[1]'', ''int'') AS ' + T2.loc.value('local-name(.)', 'varchar(20)') as Campo
    FROM @tabela AS T1
    CROSS APPLY T1.linha.nodes('/row/*') as T2(loc)
) AS T3;

SET @sql = 'SELECT ' + @campos + ' FROM @tabela';
EXEC sp_executesql @sql

Answer 2

In a more simplified way, you can select directly in xml, and for your case, we have 2 possibilities as below:

In the case of only one line return in select:

--TABELA TEMPORARIA
DECLARE @tbTemp TABLE  (Id INT, ST_Registro_Del XML)

--POPULANDO TABELA
INSERT INTO @tbTemp
SELECT  1, '<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>'

INSERT INTO @tbTemp
SELECT  2, '<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>'

--PEGANDO UMA LINHA ESPECIFICA
DECLARE @xml XML = (SELECT ST_Registro_Del FROM @tbTemp WHERE Id = 1)

--SELECT NO XML
SELECT  xmlData.A.value('./ID_Cota[1]', 'INT') AS ID_Cota,
        xmlData.A.value('./ID_Taxa_Plano[1]', 'INT') AS ID_Taxa_Plano,
        xmlData.A.value('./ID_Plano_Venda[1]', 'INT') AS ID_Plano_Venda,
        xmlData.A.value('./ID_Pessoa[1]', 'INT') AS ID_Pessoa
FROM    @xml.nodes('row') xmlData(A)    

Or by returning N lines it is necessary to use the cursor:

--TABELA TEMPORARIA
DECLARE @tbTemp TABLE  (Id INT, ST_Registro_Del XML)

--POPULANDO TABELA
INSERT INTO @tbTemp
SELECT  1, '<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>'

INSERT INTO @tbTemp
SELECT  2, '<row> 
  <ID_Cota>16298</ID_Cota>
  <ID_Taxa_Plano>100022</ID_Taxa_Plano>
  <ID_Plano_Venda>112020</ID_Plano_Venda>
  <ID_Pessoa>1844522</ID_Pessoa>
</row>'

--DECLARANDO TABELA DE RETORNO
DECLARE @tbRetorno TABLE (ID_Cota INT, ID_Taxa_Plano INT, ID_Plano_Venda INT, ID_Pessoa INT)

--VARIAVEIS DE CONTROLE PARA O CURSOR
DECLARE @Id INT,
@ST_Registro_Del XML


--CURSOR PARA PERCORRER OS XMLS
DECLARE CUR CURSOR FOR

SELECT  Id, ST_Registro_Del
FROM    @tbTemp

OPEN CUR
FETCH NEXT FROM CUR INTO @Id, @ST_Registro_Del

WHILE @@FETCH_STATUS = 0
BEGIN

    --INSERT SELECT NO XML
    INSERT INTO @tbRetorno
    SELECT  xmlData.A.value('./ID_Cota[1]', 'INT') AS ID_Cota,
            xmlData.A.value('./ID_Taxa_Plano[1]', 'INT') AS ID_Taxa_Plano,
            xmlData.A.value('./ID_Plano_Venda[1]', 'INT') AS ID_Plano_Venda,
            xmlData.A.value('./ID_Pessoa[1]', 'INT') AS ID_Pessoa
    FROM    @ST_Registro_Del.nodes('row') xmlData(A)    

    SET @ST_Registro_Del = NULL

FETCH NEXT FROM CUR INTO @Id, @ST_Registro_Del
END

CLOSE CUR
DEALLOCATE CUR

--SELECT DO RETORNO
SELECT  *
FROM    @tbRetorno

Answer 3

Researching about I ended up bumping into this question of SO-ENGLISH.

I performed my test as follows:

CREATE TABLE [dbo].[Teste_XML](
    [xml] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into Teste_XML(xml) values('<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>')

insert into Teste_XML(xml) values('<row><ID_Cota>162986</ID_Cota> <ID_Taxa_Plano>101</ID_Taxa_Plano> <ID_Plano_Venda>1530</ID_Plano_Venda> <ID_Pessoa>18523</ID_Pessoa> </row>')

SELECT 
cast(seuCampo as xml ).value('(/row//ID_Cota/node())[1]', 'nvarchar(max)') as ID_Cota,
cast(seuCampo as xml ).value('(/row//ID_Taxa_Plano/node())[1]', 'nvarchar(max)') as ID_Taxa_Plano,
cast(seuCampo as xml ).value('(/row//ID_Plano_Venda/node())[1]', 'nvarchar(max)') as ID_Plano_Venda,
cast(seuCampo as xml ).value('(/row//ID_Pessoa/node())[1]', 'nvarchar(max)') as ID_Pessoa
FROM Teste_XML

Upshot:

Here are some methods applied to XML in Sql-Server.