Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
Tom
What are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom
|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags are
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly to
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper elements.
> Unless you need to provide group specific properties, I find these elements
> useless and they actually make processing of the documents more expensive in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>
|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote: