xml explicit siblings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xml explicit siblings

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-21 : 09:40:42

AvdA writes "(I posted this yesterday but now I realise I should have filled it in here in order to get a response... oops sorry... I'm new. This is a slightly edited version of the original post and hopefully this outlines the question more clearly. The actual question is unchanged. )

An easy one for those of you who have had your head around SQLServer2000 XML EXPLICIT longer than me...

What I would like to achieve is an XML output that is essentially like:
<RootElement>
<OutsideLevel1 type = "the OL1 type goes here">
<OL1Sibling1>the OL1Sibling1 text goes here</OL1Sibling1>
<OL1Sibling2>the OL1Sibling2 text goes here</OL1Sibling2>
<OL1Sibling3>
<Child1ofOL1Sibling3>the Child1ofOL1Sibling3 text goes here</Child1ofOL1Sibling3>
<Child2ofOL1Sibling3>the Child2ofOL1Sibling3 text goes here</Child2ofOL1Sibling3>
</OL1Sibling3>
<OL1Sibling4>the OL1Sibling4 text goes here</OL1Sibling4>
<OL1Sibling5>the OL1Sibling5 text goes here</OL1Sibling5>
</OutsideLevel1>
</RootElement>

My sample SQL looks like this (against SQLServer - no DB required to simplify eg.):

SELECT 1 AS TAG, 0 AS PARENT,
NULL AS [RootElement!1!OutsideLevel1],
NULL AS [OutsideLevel1!2!type],
NULL AS [OutsideLevel1!2!OL1Sibling1!element],
NULL AS [OutsideLevel1!2!OL1Sibling2!element],
NULL AS [OutsideLevel1!2!OL1Sibling3!element],
NULL AS [OL1Sibling3!3!Child1ofOL1Sibling3!element],
NULL AS [OL1Sibling3!3!Child2ofOL1Sibling3!element],
NULL AS [OutsideLevel1!4!OL1Sibling4!element],
NULL AS [OutsideLevel1!4!OL1Sibling5!element]

UNION ALL

SELECT 2 , 1 ,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
NULL,
NULL,
NULL,
NULL

UNION ALL

SELECT 3 AS TAG, 2 AS PARENT,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
'the Child1ofOL1Sibling3 text goes here',
'the Child2ofOL1Sibling3 text goes here',
NULL,
NULL

UNION ALL

SELECT 4 AS TAG, 2 AS PARENT,
NULL,
'the OL1 type goes here',
'the OL1Sibling1 text goes here',
'the OL1Sibling2 text goes here' ,
NULL,
NULL,
NULL,
'the OL1Sibling4 text goes here',
'the OL1Sibling5 text goes here'

order by TAG
FOR XML EXPLICIT

which results in:
<RootElement>
<OutsideLevel1 type = "the OL1 type goes here">
<OL1Sibling1>the OL1Sibling1 text goes here</OL1Sibling1>
<OL1Sibling2>the OL1Sibling2 text goes here</OL1Sibling2>
<OL1Sibling3>
<Child1ofOL1Sibling3>the Child1ofOL1Sibling3 text goes here</Child1ofOL1Sibling3>
<Child2ofOL1Sibling3>the Child2ofOL1Sibling3 text goes here</Child2ofOL1Sibling3>
</OL1Sibling3>
<OutsideLevel1>
<OL1Sibling4>the OL1Sibling4 text goes here</OL1Sibling4>
<OL1Sibling5>the OL1Sibling5 text goes here</OL1Sibling5>
</OutsideLevel1>
</OutsideLevel1>
</RootElement>

As you can see there is an extra OutsideLevel1 tag that has slipped in and pushed OL1Siblings4 and 5 in a level.

I have tried a variety of orderings but I haven't managed to get one to produce the desired output.

Actually the real XML will have a whole lot more OutsideLevels with contents similarly formatted to this one. But I should be able to extrapolate from here....

So what am I doing wrong?

Thanks AvdA"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources