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
– JohnRC, Commented Jun 21, 2018 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
– Rick, Commented Jun 21, 2018 at 17:43

LukStorms · Accepted Answer · 2018-06-21 19:35:13Z

1

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;

edited Jun 21, 2018 at 19:35

answered Jun 21, 2018 at 17:18

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rick Over a year ago

The Type is not part of my 1st procedure. The process is, I have to insert node based on @type in 2nd procedure

LukStorms Over a year ago

From that second part, it seems you want to select from the #final XML only those based on the @type variable? Is that it?

Rick Over a year ago

Yes, I want to insert the @type on the final select

LukStorms Over a year ago

@Rick Updated. Gone the Dynamic SQL route. But I guess using a while loop like in this old SO post 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...)

Gottfried Lesigang · Accepted Answer · 2018-06-22 07:45:41Z

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>

Collectives™ on Stack Overflow

Update Temp table to Insert node in XML data using SQL

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related