SQL Server : convert XML data onto table
<p>I need help in fine-tuning this code that I write. I am new to SQL Server and I believe there are better ways to do this or perhaps some of the following codes can be simplified or fine-tuned for performances or saving memory resources. </p>

<p>Basically, I have this XML data :  </p>

<pre><code>&lt;table_result id="001" subj_cd="cdaaa" grade="b" name="Phua Chu Kang"/&gt;
</code></pre>

<p><a href="https://i.stack.imgur.com/vX8O9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vX8O9.png" alt="enter image description here"></a></p>

<p>and I want to create a table which looks like this from that XML data</p>

<p><a href="https://i.stack.imgur.com/ie8pS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ie8pS.png" alt="enter image description here"></a></p>

<p>Please note on the following points :</p>

<ul>
<li><code>SplitThis</code> is not a built in function (check the code below).  </li>
</ul>

<p>The data can have space, but delimited by <code>"</code>. Do note as well that the XML data can have varying number of fields-data pairs for that particular given table - referred as #dummy in the following codes. i.e. example XML data above have 4 fields (id, subj_cd, grade, name) and the next XML data could have 5 fields (i.e. id, name, occupation, phone_no, address). In the following code, #table_result is created to match the example XML data for easier demonstration. In other words, the table structures are known..so I can ignore the field names from the XML data and focus on extracting the data itself.</p>

<p>The code ran well on SQL Server 2012 (you can copy and paste run code directly) and I am able to get as above. I just need to fine tune this, if possible. I have include line like this : - - test blabla. You can uncomment that and try. I could use enhancements such as in term of avoiding the number of temp tables used or any ways to replace the use of <code>row_number()</code> in the code.  </p>

<pre><code>/* remove all temp tables */
declare @sql varchar(5000)
SELECT @sql = isnull(@sql+';', '') + 'drop table ' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
AND t.id =OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));
exec (@sql)
/* end */

/* function */
drop function splitthis
go
create function splitthis(@separator char(1), @list varchar(max))
     returns @returntable table(item nvarchar(max))
as
begin
    declare @index int
    declare @newtext varchar(max) 
    if @list = null
        return
    set @index = charindex(@separator, @list)
    while not(@index = 0)
    begin
        set @newtext = rtrim(ltrim(left(@list, @index - 1)))
        set @list = right(@list, len(@list) - @index)
        insert into @returntable(item) values(@newtext)
        set @index = charindex(@separator, @list)
    end
    insert into @returntable(item) values(rtrim(ltrim(@list)))
    update @returntable set item='' where item is null 
    return
end
go
/* end of function */

/* create dummy tables */
create table #table_result 
(id nvarchar(max), subj_cd nvarchar(max), grade nvarchar(max), name nvarchar(max))

create table #dummy (name nvarchar(max), data nvarchar(max))
insert into #dummy 
values ('a', '&lt;table_result id="001" subj_cd="cdaaa" grade="b" name="phua chu kang"/&gt;');
--test : select * from #dummy

/* remove the fist non-data opening tag */
declare @record nvarchar(max)
select @record = data from #dummy where name = 'a'
select *, null as temp into #tempb from splitthis(' ',@record)
select *, row_number() over (order by temp) count into #tempc from #tempb
select item into #tempd from #tempc where #tempc.count&gt;1
-- test : select * from #tempd

/* get the actual field &amp; data into a single column table */
declare @temp varchar(max)
set @temp=''select @temp=@temp+' ' + item from #tempd 
select *, null as temp into #tempe from splitthis('"',@temp)
select *, row_number() over (order by temp) count into #tempf from #tempe
select item, count into #tempg from #tempf
--test : select * from #tempg

/* prepare the data table */
select 
    case when #tempg.count % 2 = 0 
        then item
        else null
    end as data
into #temph
from #tempg 
select data, null as temp into #tempi from #temph
select data, row_number() over (order by temp) count into #data from #tempi
    where data is not null
--test : select * from #data

/* prepare the field table. */
select name, null as temp into #tempj 
from tempdb.sys.columns where object_id=object_id('tempdb..#table_result');
select *, row_number() over (order by temp) count into #field from #tempj
--test : select * from #field

/* get the final table */
select a.name as field, b.data from #field a
left join #data b on a.count=b.count
</code></pre>