XML Newbie Alert! FOR XML EXPLICIT help please

I

iamaran

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!
 
I

iamaran

<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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help please 8
Can't solve problems! please Help 0
Help Needed with VBA please help 0
xml to html 6
I need help in understanding these files on my phone, Could someone help me understand these files? Urgent help needed. Please help. 1
Need help to write data onto an XML file after reading data fromanother xml file 5
Pygame animation help!!!! 0
XML to XML Filtering using XSL Problem 0

Members online

Total: 32 (members: 1, guests: 31)
Robots: 510

Forum statistics

Threads
473,769
Messages
2,569,579
Members
45,053
Latest member
BrodieSola

Latest Threads

Top