f



XML Newbie Alert! FOR XML EXPLICIT help please

Apologies for the newbie question. I have read and re-read about FOR XML 
EXPLICIT and now my head hurts!

This is an example of the XML I am trying to form from a very simple SQL 
table:

<Record>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
<HeaderData2>HeaderStuff</HeaderData2>
</RecordHeader>
<RecordBody> 
<Body Data>BodyStuff</BodyData>
<Body Data2>BodyStuff</BodyData2>
</RecordBody>
</Record>

<Record>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
<HeaderData2>HeaderStuff</HeaderData2>
</RecordHeader>
<RecordBody> 
<Body Data>BodyStuff</BodyData>
<Body Data2>BodyStuff</BodyData2>
</RecordBody>
</Record>


I have tried many variants of:

select 1 as tag, NULL as parent, HeaderStuff AS [RecordHeader!1!HeaderData!
element], HeaderStuff2 AS [RecordHeader!1!HeaderData2!element], 
BodyStuff [RecordBody!1!BodyData!element],BodyStuff2 [RecordBody!1!
BodyData2!element] 
FROM tbl_test
FOR XML EXPLICIT

But am getting nowhere.
I realise I am redeclaring XML tag ID1, but other variants I try are not 
much better!.

Also, how do I get the <Record></Record> ?

Can anyone help me? 
Thanks in advance if you can spare a minute for someone who needs help!
0
12/22/2005 7:00:47 PM
comp.text.xml 8781 articles. 0 followers. Post Follow

1 Replies
625 Views

Similar Articles

[PageSpeed] 54

iamaran <iamaranNOSPAM@hotmail.com> wrote in 
news:Xns9734C2086A447iamaranNOSPAMhotmail@62.253.170.163:

<snip>

So now I'm doing something along the lines of:

SELECT Tag = 1, Parent = NULL,
[Record!1!] = NULL,
[RecordHeader!2!] = NULL,
[RecordHeader!2!HeaderData] = NULL,
[RecordHeader!2!HeaderData2] = NULL,
[RecordBody!3!] = NULL,
[RecordBody!3!BodyData] = NULL,
[RecordBody!3!BodyData2] = NULL

UNION ALL
 
SELECT Tag = 2, Parent = 1,
[Record!1!] = NULL,
[RecordHeader!2!] = NULL,
[RecordHeader!2!HeaderData] = aaa,
[RecordHeader!2!HeaderData2] = bbb,
[RecordBody!3!] = NULL,
[RecordBody!3!BodyData] = NULL,
[RecordBody!3!BodyData2] = NULL

FROM tbl_test
UNION ALL
 
SELECT Tag = 3, Parent = 1,
[Record!1!] = NULL,
[RecordHeader!2!] = NULL,
[RecordHeader!2!HeaderData] = NULL,
[RecordHeader!2!HeaderData2] = NULL,
[RecordBody!3!] = NULL,
[RecordBody!3!BodyData] = ccc,
[RecordBody!3!BodyData2] = ddd

FROM tbl_test
FOR XML EXPLICIT


and that a lot better. But instead of getting:


<Record>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
<HeaderData2>HeaderStuff</HeaderData2>
</RecordHeader>
<RecordBody> 
<Body Data>BodyStuff</BodyData>
<Body Data2>BodyStuff</BodyData2>
</RecordBody>
</Record>
<Record>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
....
....
</Record>


I'm getting
<Record>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
<HeaderData2>HeaderStuff</HeaderData2>
</RecordHeader>
<RecordHeader>
<HeaderData>HeaderStuff</HeaderData>
<HeaderData2>HeaderStuff</HeaderData2>
</RecordHeader>
<RecordBody> 
<Body Data>BodyStuff</BodyData>
<Body Data2>BodyStuff</BodyData2>
</RecordBody>
<RecordBody> 
<Body Data>BodyStuff</BodyData>
<Body Data2>BodyStuff</BodyData2>
</RecordBody>
</Record>

Can anyone help at all? Thanks.

0
12/23/2005 8:17:36 PM
Reply: