Re: Nesting dis-similar hierarchies
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 18 May 2005 08:00:34 -0700
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@xxxxxxxxxxxxxxxx
>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
.
- References:
- Nesting dis-similar hierarchies
- From: Tom Heavey
- Nesting dis-similar hierarchies
- Prev by Date: Nesting dis-similar hierarchies
- Next by Date: OPENXML debugging
- Previous by thread: Nesting dis-similar hierarchies
- Next by thread: OPENXML debugging
- Index(es):
Relevant Pages
|
