Cross Apply At Different Level

Question

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 that has the columns in this order: home,away,homeID, awayID, homeScore, awayScore. I can't seem to get the home id (363 and 375) into 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

Pரதீப் · Accepted Answer · 2015-01-01T04:28:05.877

You missed to mention the position for Id.

value() always requires a positional reference to identify the node you want.

Change your select like this.

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

Much neater than what I was constructing - nicely done. Stylistically, perhaps either all or none of the *cs.* aliases should be included, rather than half and half. — Pieter Geerkens, Jan 01 '15 at 04:25

Cross Apply At Different Level

1 Answers1