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
You want to display all XML in a select?
– Marconi
Please post xml instead of image.
– Marconi
Use the edit button.
– Marconi
The implementation of XML handling in SQL Server was done in SQL Server 2016. Is it necessary to update? It will be much easier to solve this.
– Thiago Lunardi
@Varaujo, add the XML code in the body of the question, and not in the comments, please.
– Thiago Lunardi
@Thiago Lunardi, we just migrated the version of Sql Server, unfortunately we will not update at this time.
– VAraujo
@Varaujo added an answer.
– Marconi