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