XML parse using SQL

Question

How can i get all values in this xml. Ihave tried everything but i cannot get it to work. Any one can give me quick answer? It should return at least for rows.

DECLARE @x XML =
    '<Events>
     <Event DateTimeGMT="25/10/2013 18:45:00" Branch="Soccer" Sport="Soccer" BranchID="1" League="England - Championship" LeagueID="10099" ID="5693075" IsOption="0" EventType="0" MEID="2673883">
    <Participants>
      <Participant1 Name="Middlesbrough" Home_Visiting="Home" />
      <Participant2 Name="Doncaster" Home_Visiting="Visiting" />
    </Participants>
    <MoneyLine Home="1.69" Draw="3.7" Away="5" />
    <Spread Home_Odds="1.885" Home_Points="-0.75" Away_Points="0.75" Away_Odds="1.962" />
    <Total Points="2.75" Over="2.06" Under="1.763" />
  </Event>
  <Event DateTimeGMT="25/10/2013 18:45:00" Branch="Soccer" Sport="Soccer" BranchID="1" League="England - Championship" LeagueID="10099" ID="5693993" IsOption="1" EventType="200" MEID="2673883">
    <Participants>
      <Participant1 Name="Middlesbrough" Home_Visiting="Home" />
      <Participant2 Name="Doncaster" Home_Visiting="Visiting" />
    </Participants>
    <Total Points="4.5" Over="5.75" Under="1.125" />
  </Event>
  <Event DateTimeGMT="25/10/2013 18:45:00" Branch="Soccer" BranchID="1" League="England - Championship" LeagueID="10099" QAID="4423106" EventType="38" EventName="Middlesbrough vs Doncaster">
    <Participants>
      <Participant Name="Odd">
        <Odds TypeName="Odd/Even" OddsValue="1.909" />
      </Participant>
      <Participant Name="Even">
        <Odds TypeName="Odd/Even" OddsValue="1.8" />
      </Participant>
       .
       .
       .
      <Participant Name="0:0">
        <Odds TypeName="Exact" OddsValue="1.8" />
      </Participant>
    </Participants>
  </Event>
</Events>'

DECLARE @iDoc INT
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @x

SELECT  *
FROM    OPENXML(@iDoc,'/Events/Event/Participants')
WITH    (
ID int '../@ID',
DateTimeGMT [varchar](100) '../@DateTimeGMT',
Branch [varchar](100) '../@Branch',
Sport [varchar](100) '../@Sport',
BranchID int '../@BranchID',
League [varchar](100) '../@League',
LeagueID int '../@LeagueID',
IsOption int '../@IsOption',
EventType int '../@EventType',
MEID int '../@MEID',
QAID int '../@QAID',
EventName [varchar](500) '../@EventName',
Home [varchar](100) '..//Participant1/@Name',
Away [varchar](100) '..//Participant2/@Name',
[1] [varchar](5) '..//MoneyLine/@Home',
[X] [varchar](5) '..//MoneyLine/@Draw',
[2] [varchar](5) '..//MoneyLine/@Away',
Spread_Home_Points float '..//Spread/@Home_Points',
Spread_Home_Odds float '..//Spread/@Home_Odds',
Spread_Away_Points float '..//Spread/@Away_Points',
Spread_Away_Odds float '..//Spread/@Away_Odds',
Total_Points float '..//Total/@Points',
Lart float '..//Total/@Over',
Posht float '..//Total/@Under',
Rubrika varchar(100) 'Participant/@Name',
Koefic varchar(100) 'Participant/Odds/@OddsValue'
        )

This doesnt read all values in this node

Rubrika varchar(100) 'Participant/@Name',
    Koefic varchar(100) 'Participant/Odds/@OddsValue'

It should return more rows

enter image description here

roman · Accepted Answer · 2013-10-26 12:35:39Z

If I understand what you need, here's a query you want:

select
    T.C.value('@DateTimeGMT', 'varchar(100)') as DateTimeGMT,
    T.C.value('@Branch', 'varchar(100)') as Branch,
    T.C.value('@BranchID', 'int') as BranchID,
    T.C.value('@League', 'varchar(100)') as League,
    T.C.value('@LeagueID', 'int') as LeagueID,
    T.C.value('@IsOption', 'int') as IsOption,
    T.C.value('@EventType', 'int') as EventType,
    T.C.value('@MEID', 'int') as MEID,
    T.C.value('@QAID', 'int') as QAID,
    T.C.value('@EventName', 'varchar(500)') as EventName,
    T.C.value('(Participants/Participant1)[1]/@Name', 'varchar(100)') as Home,
    T.C.value('(Participants/Participant2)[1]/@Name', 'varchar(100)') as Away,
    T.C.value('(MoneyLine)[1]/@Home', 'varchar(5)') as [1],
    T.C.value('(MoneyLine)[1]/@Draw', 'varchar(5)') as [X],
    T.C.value('(MoneyLine)[1]/@Away', 'varchar(5)') as [2],
    T.C.value('(Spread)[1]/@Home_Points', 'float') as Spread_Home_Points,
    T.C.value('(Spread)[1]/@Home_Odds', 'float') as Spread_Home_Odds,
    T.C.value('(Spread)[1]/@Away_Points', 'float') as Spread_Away_Points,
    T.C.value('(Spread)[1]/@Away_Odds', 'float') as Spread_Away_Odds,
    T.C.value('(Spread)[1]/@Away_Odds', 'float') as Spread_Away_Odds,
    T.C.value('(Total)[1]/@Points', 'float') as Total_Points,
    T.C.value('(Total)[1]/@Over', 'float') as Lart,
    T.C.value('(Total)[1]/@Under', 'float') as Posht,
    P.C.value('@Name', 'varchar(100)') as Rubrika,
    P.C.value('(Odds)[1]/@OddsValue', 'varchar(100)') as Koefic
from @x.nodes('Events/Event') as T(C)
    outer apply T.C.nodes('Participants/Participant') as P(C)

Note that is parses Events/Event first and then duplicate rows for each Participants/Participant found.

sql fiddle demo

Collectives™ on Stack Overflow

XML parse using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related