Update Temp table to Insert node in XML data using SQL

Question

My question is related to this question. However I am writing a clean scenario on which I need help. I am a beginner in SQL and pardon me If I'm incorrect anywhere.

I have a procedure(huge on. replicating a small sample here) that spits out bunch of XMLs. On my second procedure, based on Parametric condition I have to insert a node in the XML for every CID and do a final select. I am adding a Rextester link below:

UPDATED LINK: http://rextester.com/EFGQB11125

Current output:

  <Main>
    <ID>1001</ID>
    <details>
        <name>John</name>
        <age>12</age>
    </details>
</Main>
<Main>
    <ID>1002</ID>
    <details>
        <name>Rick</name>
        <age>19</age>
    </details>
</Main>
<Main>
    <ID>1003</ID>
    <details>
        <name>Diane</name>
        <age>25</age>
    </details>
</Main>
<Main>
    <ID>1004</ID>
    <details>
        <name>Kippy</name>
        <age>26</age>
    </details>
</Main>

Desired output:

When @type = 'N'/'U' then

    <Main>
    <ID>1001</ID>
    <type>N</type>
    <details>
        <name>John</name>
        <age>12</age>
    </details>
</Main>
<Main>
    <ID>1002</ID>
    <type>U</type>
    <details>
        <name>Rick</name>
        <age>19</age>
    </details>
</Main>
<Main>
    <ID>1003</ID>
    <type>N</type>
    <details>
        <name>Diane</name>
        <age>25</age>
    </details>
</Main>
<Main>
    <ID>1004</ID>
    <type>N</type>
    <details>
        <name>Kippy</name>
        <age>26</age>
    </details>
</Main>

So, for every CID, based on @type values, a node should be inserted with that value. Any help?!

(Sorry for being redundant by any chance)

Looking at the SQL code I think you have a couple of problems. A) The temp table #final goes out of scope when Stored Proc 1 is exited so will not be available to Stored Proc 2 unless SP2 is called from SP1 B) #final has only a single record containing the generated XML from SP1. So you will not be able to process nodes of the XML within a Select Statement directly from the #final table - I think you will need to load the XML text into an XML variable and process it from there. I suggest you might find it easier just to use SQL tables then generate the complete XML from SP2 at the end. — JohnRC, Jun 21 '18 at 17:42
@JohnRC you are right. That was the option that I considered first. However, the sample I posted here is small and the actual Sproc that I have spits out 300 node XML. It will be super tedious to convert them back and forth as tables. — Rick, Jun 21 '18 at 17:43

LukStorms · Accepted Answer · 2018-06-21T19:35:13.783

The trick used in the example below uses a bit of Dynamic Sql magic.

Basically building 1 big SQL with all the updates for each ID.

declare @Sql varchar(max); 

select @Sql = concat(@Sql,'UPDATE #final SET xml_data.modify(''insert <type>',ctype,'</type> after (/Main/ID)[text()="',cid,'"][1]'');',CHAR(13),CHAR(10))
from #tbl;

-- select @Sql as xml_modify_sqls;
exec(@Sql);

select * from #final;

And here's some SQL to setup the temporary tables with the sample data:

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL DROP TABLE #tbl;
create table #tbl (cid int, ctype varchar(5));
IF OBJECT_ID('tempdb..#cdetails') IS NOT NULL DROP TABLE #cdetails;
create table #cdetails (cid int, name varchar(5), age int);
IF OBJECT_ID('tempdb..#final') IS NOT NULL DROP TABLE #final;
create table #final (xml_data xml);
insert into #tbl (cid, ctype) values
(1001,'N'), (1002,'U'), (1003,'N'), (1004,'N');
insert into #cdetails (cid, name, age) values
(1001,'John',12), (1002,'Rick',19), (1003,'Diane',25), (1004,'Kippy',26);
insert into #final (xml_data)
select xml_data
from (
    select 
    cd1.cid as ID,
    -- type = t.ctype as type,
    details =
    (
        select 
         cd.name,
         cd.age
        from #cdetails cd
        where cd.cid = cd1.cid
        For XML Path(''), Type
    )
    from #cdetails cd1
    join #tbl t on cd1.cid = t.cid
    For XML Path('Main')
) q(xml_data);
select * from #final;

The Type is not part of my 1st procedure. The process is, I have to insert node based on @type in 2nd procedure — Rick, Jun 21 '18 at 17:19
From that second part, it seems you want to select from the #final XML only those based on the @type variable? Is that it? — LukStorms, Jun 21 '18 at 17:24
@Rick Updated. Gone the Dynamic SQL route. But I guess using a while loop like in [this old SO post](https://stackoverflow.com/questions/16712841/) could also work. The problem is that it seems impossible to use the modify function for multiple nodes at the same time. (such a PITA...) — LukStorms, Jun 21 '18 at 19:18

score 1 · Answer 2 · answered Jun 22 '18 at 07:45

In your case it is much easier to read the data out of the XML and re-build it from scratch:

;WITH cte AS
(
       select m.value(N'(ID/text())[1]','int') AS ID
             ,m.value(N'(details/name)[1]','nvarchar(max)') AS DetailsName
             ,m.value(N'(details/age)[1]','int') AS DetailsAge
             ,t.ctype
       from #final f
       CROSS APPLY f.xml_data.nodes(N'/Main') A(m)
       LEFT JOIN #tbl t ON t.cid=m.value(N'(ID/text())[1]','int') --left join, because I don't know if and ID is found as CID
)
SELECT ID
      ,ctype AS [type] 
      ,DetailsName AS [details/name]
      ,DetailsAge AS [details/age]
FROM cte
FOR XML PATH('Main')

returns

<Main>
  <ID>1001</ID>
  <type>N</type>
  <details>
    <name>John</name>
    <age>12</age>
  </details>
</Main>
<Main>
  <ID>1002</ID>
  <type>U</type>
  <details>
    <name>Rick</name>
    <age>19</age>
  </details>
</Main>
<Main>
  <ID>1003</ID>
  <type>N</type>
  <details>
    <name>Diane</name>
    <age>25</age>
  </details>
</Main>
<Main>
  <ID>1004</ID>
  <type>N</type>
  <details>
    <name>Kippy</name>
    <age>26</age>
  </details>
</Main>

Update Temp table to Insert node in XML data using SQL

2 Answers2