How to insert xml tag within a sql server table

Question

I am trying to insert an XML tag that is allocated within a field in an SQL table (SQL SERVER)

I am currently trying so but am unsuccessful:

update UserData set tp_ColumnSet.modify('insert <uniqueidentifier2>E2BE0475-2ED2-459D-ACC8-D46A703B605B</uniqueidentifier2> into (/tp_ColumnSet[1])') where  tp_ColumnSet.value('(nvarchar1)[1]', 'nvarchar(300)') = 'Ihara_1571-000_Proposta_Serviço_E-Social '

---

Currently it is like this:

<datetime2>2019-05-06T17:53:27</datetime2>    
<datetime3>2019-05-06T17:53:27</datetime3>    
<datetime4>2019-05-06T03:00:00</datetime4>    
<int6>21454</int6>    
<int12>21454</int12>    
<int13>21454</int13>    
<int16>6</int16>    
<int18>4</int18>    
<nvarchar1>Ihara_1571-000_Proposta_Serviço_E-Social </nvarchar1>    
<nvarchar3>Semanal</nvarchar3>    
<nvarchar4>Projeto</nvarchar4>    
<nvarchar7>Aguardando Análise</nvarchar7>    
<nvarchar8>Aguardando Análise</nvarchar8>    
<nvarchar9>Aprovado</nvarchar9>    
<nvarchar10>Muito Baixo</nvarchar10>    
<nvarchar11>Muito Baixo</nvarchar11>    
<nvarchar12>Muito Baixo</nvarchar12>
<nvarchar13>Muito Baixo</nvarchar13>    
<nvarchar14>Muito Baixo</nvarchar14>    
<nvarchar18>Aprovação da Diretoria</nvarchar18>    
<nvarchar19>Diretoria</nvarchar19>    
<nvarchar22>1-612P</nvarchar22>    
<nvarchar25>㮭⩾퀖䟴릚仰ࣔ</nvarchar25>    
<nvarchar26>ڇ쀠앾䭈⻌듯</nvarchar26>    
<nvarchar27>⥧쌪ꞻ䷪㊤뱙還礿</nvarchar27>    
<nvarchar32>/PWA/_layouts/15/wrkstat.aspx?List=3c39c3c7-c966-46f3-93f0-2fd071c47fd3&amp;WorkflowInstanceName=75287446-d222-4a70-92c6-024a7a06e2db</nvarchar32>    
<nvarchar33>Preparar 1</nvarchar33>

And I would need to insert this tag at the end : <uniqueidentifier2>E2BE0475-2ED2-459D-ACC8-D46A703B605B</uniqueidentifier2>

Answer 1

You can do it like this:

DROP TABLE ##yourTable

CREATE TABLE ##yourTable
(XMLCol XMl);

Insert into ##yourTable 
VALUES(
N'<datetime2>2019-05-06T17:53:27</datetime2>    
<datetime3>2019-05-06T17:53:27</datetime3>    
<datetime4>2019-05-06T03:00:00</datetime4>    
<int6>21454</int6>    
<int12>21454</int12>    
<int13>21454</int13>    
<int16>6</int16>    
<int18>4</int18>    
<nvarchar1>Ihara_1571-000_Proposta_Serviço_E-Social </nvarchar1>    
<nvarchar3>Semanal</nvarchar3>    
<nvarchar4>Projeto</nvarchar4>    
<nvarchar7>Aguardando Análise</nvarchar7>    
<nvarchar8>Aguardando Análise</nvarchar8>    
<nvarchar9>Aprovado</nvarchar9>    
<nvarchar10>Muito Baixo</nvarchar10>    
<nvarchar11>Muito Baixo</nvarchar11>    
<nvarchar12>Muito Baixo</nvarchar12>
<nvarchar13>Muito Baixo</nvarchar13>    
<nvarchar14>Muito Baixo</nvarchar14>    
<nvarchar18>Aprovação da Diretoria</nvarchar18>    
<nvarchar19>Diretoria</nvarchar19>    
<nvarchar22>1-612P</nvarchar22>    
<nvarchar25>㮭⩾퀖䟴릚仰ࣔ</nvarchar25>    
<nvarchar26>ڇ쀠앾䭈⻌듯</nvarchar26>    
<nvarchar27>⥧쌪ꞻ䷪㊤뱙還礿</nvarchar27>    
<nvarchar32>/PWA/_layouts/15/wrkstat.aspx?List=3c39c3c7-c966-46f3-93f0-2fd071c47fd3&amp;WorkflowInstanceName=75287446-d222-4a70-92c6-024a7a06e2db</nvarchar32>    
<nvarchar33>Preparar 1</nvarchar33>')


select XMLCol from ##yourTable

UPDATE ##yourTable SET XMLCol = (
    SELECT CONCAT((
        SELECT CAST(XMLCol as nvarchar(max))FROM ##yourTable), '<uniqueidentifier2>E2BE0475-2ED2-459D-ACC8-D46A703B605B</uniqueidentifier2>'))


select XMLCol from ##yourTable

XML file saved in column:

What this does is transform the XML in a nvarchar, concatenate the desired tag and update the table with the new value.