XML Newbie Alert! FOR XML EXPLICIT help please (1 Viewer)

aranj · Dec 22, 2005

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

This is 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 I can't get it any better.

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

Can anyone help me?
Thanks in advance if you can spare the time for someone who needs help!

aranj · Dec 23, 2005

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.

XML Newbie Alert! FOR XML EXPLICIT help please (1 Viewer)

aranj

Registered User.

aranj

Registered User.

Similar threads

Users who are viewing this thread