Get value to show inside current xml node instead of adding a new xml node within the current xml node



So here is the issue I'm having and I hope this makes sense. I wrote a sql script to export data from tables into one xml file using for xml path.


Here is an example (You should be able to just copy, paste and run the below script):



create table #temptable(
mykey nvarchar(200),
myarea nvarchar(200),
mytype nvarchar(200),
myvalue nvarchar(max)
)

insert into #temptable values ('6385465665245', 'area1', 'type1', 'This area should be inside the keyareatypes node and NOT in the value node.')
insert into #temptable values ('6632525685488', 'area2', 'type2', 'This area should be inside the keyareatypes node and NOT in the value node.')

select
tmp.mykey as '@key',
tmp.myarea as '@area',
tmp.mytype as '@type',
tmp.myvalue as 'value'

from
#temptable tmp

for xml path('keyareatypes'), type

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
DROP TABLE #temptable


if I run the above query, I get the following:



<keyareatypes key="6385465665245" area="area1" type="type1">
<value>This area should be inside the keyareatypes node and NOT in the value node.</value>
</keyareatypes>
<keyareatypes key="6632525685488" area="area2" type="type2">
<value>This area should be inside the keyareatypes node and NOT in the value node.</value>
</keyareatypes>


As you can see, in the <keyareatypes> node is another node called <value>. I do not want the <value> node there.


Any help will be greatly appreciated.


Thanks.


No comments:

Post a Comment