The cross is applied at different levels
I have xml data that looks like this:
<game>
<teams>
<home id="363" color="000099">Brazil</home>
<away id="375" color="c5b358">Germany</away>
</teams>
<gameInfo>
<homeScore>1</homeScore>
<awayScore>7</awayScore>
<clock>90</clock>
</gameInfo>
</game>
I would like to create a table with columns in this order: home, away, homeID, awayID, homeScore, awayScore. I can't seem to get the house id (363 and 375) in the table:
select *
from (
select
e.value('(./teams/home/text())[1]', 'nvarchar(100)') home,
e.value('(./teams/away/text())[1]', 'nvarchar(100)') away,
e.value('./teams/home/@id', 'int') homeID,
e.value('./teams/away/@id', 'int') awayID
e.value('(./gameInfo/homeScore/text())[1]', 'int') homeScore,
e.value('(./gameInfo/awayScore/text())[1]', 'int') awayScore
from (select * from [XMLTest].[dbo].[MATCHES]) t
cross apply t.data.nodes('game') as t2(e)
) events
+3
source to share
1 answer
You missed mentioning the position for Id
.
value()
always needs the positional
identification link node
you want.
Change yours select
as follows.
SELECT e.value('(./teams/home/text())[1]', 'nvarchar(100)') home,
e.value('(./teams/away/text())[1]', 'nvarchar(100)') away,
e.value('(./teams/home/@id)[1]', 'int') homeID,
e.value('(./teams/away/@id)[1]', 'int') awayID,
e.value('(./gameInfo/homeScore/text())[1]', 'int') homeScore,
e.value('(./gameInfo/awayScore/text())[1]', 'int') awayScore
FROM [MATCHES] t
CROSS apply t.data.nodes('game') AS t2(e)
Example:
DECLARE @xml XML='<game>
<teams>
<home id="363" color="000099">Brazil</home>
<away id="375" color="c5b358">Germany</away>
</teams>
<gameInfo>
<homeScore>1</homeScore>
<awayScore>7</awayScore>
<clock>90</clock>
</gameInfo>
</game>'
SELECT cs.e.value('(./teams/home)[1]', 'nvarchar(100)') home,
cs.e.value('(./teams/away)[1]', 'nvarchar(100)') away,
cs.e.value('(./teams/home/@id)[1]', 'int') homeID,
cs.e.value('(./teams/away/@id)[1]', 'int') awayID,
cs.e.value('(./gameInfo/homeScore)[1]', 'int') homeScore,
cs.e.value('(./gameInfo/awayScore)[1]', 'int') awayScore
FROM @xml.nodes('game') AS cs (e)
Result:
home away homeID awayID homeScore awayScore
------ ------- ------ ------ --------- ---------
Brazil Germany 363 375 1 7
+2
source to share