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