SQL Server : convert XML data onto table

Question

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.

Basically, I have this XML data :

<table_result id="001" subj_cd="cdaaa" grade="b" name="Phua Chu Kang"/>

and I want to create a table which looks like this from that XML data

Please note on the following points :

• SplitThis is not a built in function (check the code below).

The data can have space, but delimited by ". 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.

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 row_number() in the 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', '<table_result id="001" subj_cd="cdaaa" grade="b" name="phua chu kang"/>');
--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>1
-- test : select * from #tempd

/* get the actual field & 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

Answer 1

This is - using XML methods - much easier!

Try this:

DECLARE @xml XML='<table_result id="001" subj_cd="cdaaa" grade="b" name="Phua Chu Kang"/>';

SELECT One.Attr.value('fn:local-name(.)','varchar(max)') AS field
      ,One.Attr.value('.','varchar(max)') AS data
FROM @xml.nodes('table_result/@*') AS One(Attr)

The result

field     data
id        001
subj_cd   cdaaa
grade     b
name      Phua Chu Kang

Now I try to imitate your table structure (I'd recommend to store the data as XML from the beginning! In this case you could omit the first CROSS APPLY with the CAST ... AS XML):

DECLARE @tbl TABLE(name VARCHAR(10),data VARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('a','<table_result id="001" subj_cd="cdaaa" grade="b" name="Phua Chu Kang"/>') 
,('b','<Another test="test data" test2="test2 data"/>') 
,('c','<OneMore x="x data" y="y data" z="z data"/>'); 

SELECT tbl.name
      ,One.Attr.value('fn:local-name(..)','varchar(max)') AS element
      ,One.Attr.value('fn:local-name(.)','varchar(max)') AS field
      ,One.Attr.value('.','varchar(max)') AS data
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(tbl.data AS XML)) AS MyData(AsXml)
CROSS APPLY MyData.AsXml.nodes('*/@*') AS One(Attr)

The result

name  element      field    data
a     table_result  id      001
a     table_result  subj_cd cdaaa
a     table_result  grade   b
a     table_result  name    Phua Chu Kang
b     Another       test    test data
b     Another       test2   test2 data
c     OneMore       x       x data
c     OneMore       y       y data
c     OneMore       z       z data

Answer 2

Now, I'm not at all very good with T-SQL XML, but can't you just do it like this:

create table #dummy (name nvarchar(max), data xml);

insert into #dummy 
values ('a', '<table_result id="001" subj_cd="cdaaa" grade="b" name="phua chu kang"/>');

select 'id' "field",
    elem.value('@id', 'nvarchar(50)') "data"
from #dummy
cross apply data.nodes('/table_result') tbl(elem)

union all

select 'subj_cd' "field",
    elem.value('@subj_cd', 'nvarchar(50)') "data"
from #dummy
cross apply data.nodes('/table_result') tbl(elem)

union all

select 'grade' "field",
    elem.value('@grade', 'nvarchar(50)') "data"
from #dummy
cross apply data.nodes('/table_result') tbl(elem)

union all

select 'name' "field",
    elem.value('@name', 'nvarchar(50)') "data"
from #dummy
cross apply data.nodes('/table_result') tbl(elem);

Notice that I changed the data type for #dummy.data to be xml. That's required to be able to use the XML functions.