XML Explicit 1

[Moptop]

Hi
I am having difficulty understanding how the FOR XML EXPLICIT WORKS.

Here is my sample code:
create table #t1 (f1 char(3))
insert into #t1 values ('XXX')
insert into #t1 values ('YYY')

create table #t2 (f2 char(3))
insert into #t2 values ('AAA')

select 1 Tag, NULL Parent,
f2 [LogFiles!1!Path!element],
null [Files!2!File!element]
from #t2
union
select 2 Tag, 1 Parent,
f2 ,
f1
from #t2
inner join #t1 on 1=1
order by [LogFiles!1!Path!element],[Files!2!File!element]
for xml explicit

drop table #t2
drop table #t1


This produces:
<LogFiles><Path>AAA</Path><Files><File>XXX</File></Files><Files><File>YYY</File></Files></LogFiles>

What I would like is:
<LogFiles><Path>AAA</Path><Files><File>XXX</File><File>YYY</File></Files></LogFiles>

i.e. not having the extra "files" tag.

HELP! What am I doing wrong?

Thanks

Moptop

 

[jms8222]

Does this get you what you want:

select     1 As Tag, 
        NULL As Parent, 		
          f2 As [LogFiles!1!Path!element],
        NULL AS [Files!2!Files!hide],
        NULL AS [File!3]
from #t2
union
select     2,
           1,
        NULL,
        NULL,
        NULL
from #t2
union
select     3,
           2,
        NULL,
        NULL,
          f1 
from #t2
inner join #t1 on 1=1
order by Tag
for xml explicit

I don't know why it has to work this way, but it seems you have to nest another level in order to get rid of the extra tags. You'll also need to modify the order by (I just used tag for simplicity).

Hope this helps!!

 

[Moptop]

Thanks

That does the job
- now all I have to do is understand it

Thanks

Mark