for xml explicit question

I've written some for xml explicit sql, now it works fine but what i want to
do now is add another set of elements at level 2.
For example:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
</root>
is what works fine, but now what i want is to have:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
<blah number="1"/>
<blah number="2"/>
</root>
when i write the for xml explicit to do this, it says that the element for
level 2 is already defined. or something like that. the way i see it is
that "thingy" and "blah" are both at level 2 and so should both have a tag
of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
wouldnt that make it appear under "thingy"... as (according to my book) tag
is effectively the nesting level?
i can write out full example code for you, but just for now, is there
anything obvious that i'm missing? its amazing how little resources are out
there, that talk about unusual xml explicit code!
thanks
Paul
The number you assign in the query isn't a level - it's a tag identifier. So
tags, 1 and 2 can be at the same level, they're just different tags. The
thing that determines the level is the parent, and you can assign the same
parent to as many tags as you like.
here's an example from the Northwind database
SELECT 1 As TAG, NULL As Parent,
ProductID AS [thingy!1!Number],
NULL AS [blah!2!Number]
FROM Products
UNION
SELECT 2 AS TAG, NULL AS Parent,
NULL,
CategoryID
FROM Categories
FOR XML EXPLICIT
As you'll see from the results, both thingy and blah are at the same level.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
http://www.microsoft.com/mspress/books/6137.asp
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...
> I've written some for xml explicit sql, now it works fine but what i want
to
> do now is add another set of elements at level 2.
> For example:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> </root>
> is what works fine, but now what i want is to have:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> <blah number="1"/>
> <blah number="2"/>
> </root>
> when i write the for xml explicit to do this, it says that the element for
> level 2 is already defined. or something like that. the way i see it is
> that "thingy" and "blah" are both at level 2 and so should both have a
tag
> of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
> wouldnt that make it appear under "thingy"... as (according to my book)
tag
> is effectively the nesting level?
> i can write out full example code for you, but just for now, is there
> anything obvious that i'm missing? its amazing how little resources are
out
> there, that talk about unusual xml explicit code!
> thanks
> Paul
>
|||Thanks! I adjusted my XML accordingly and it works, beautifully.
Its amazing how simple this all is, when you get your head round it (famous
last words until my next problem, hehe). btw I found removing the for xml
explicit part and looking at the virtual (?) table is a big help on the way
to enlightenment
Thanks again
Paul
Graeme Malcolm (Content Master Ltd.) wrote:[vbcol=seagreen]
> The number you assign in the query isn't a level - it's a tag
> identifier. So tags, 1 and 2 can be at the same level, they're just
> different tags. The thing that determines the level is the parent,
> and you can assign the same parent to as many tags as you like.
> here's an example from the Northwind database
> SELECT 1 As TAG, NULL As Parent,
> ProductID AS [thingy!1!Number],
> NULL AS [blah!2!Number]
> FROM Products
> UNION
> SELECT 2 AS TAG, NULL AS Parent,
> NULL,
> CategoryID
> FROM Categories
> FOR XML EXPLICIT
> As you'll see from the results, both thingy and blah are at the same
> level.
> Hope that helps,
> Graeme
>
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...