XMl parse in SQL Server

Question

I have this xml structure.I use the following sql to read the values but it doesnt work

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>
</Events>
'
DECLARE @iDoc INT
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @x

SELECT  *
FROM    OPENXML(@iDoc,'/Events/Event')
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) '../Event/Participants/Participant1/@Name',
Away [varchar](100) '../Event/Participants/Participant2/@Name',
[1] [varchar](5) '../Event/MoneyLine/@Home',
[X] [varchar](5) '../Event/MoneyLine/@Draw',
[2] [varchar](5) '../Event/MoneyLine/@Away',
Spread_Home_Points float '../Event/Spread/@Home_Points',
Spread_Home_Odds float '../Event/Spread/@Home_Odds',
Spread_Away_Points float '../Event/Spread/@Away_Points',
Spread_Away_Odds float '../Event/Spread/@Away_Odds',
Total_Points float '../Event/Total/@Points',
Lart float '../Event/Total/@Over',
Posht float '../Event/Total/@Under'
        )

EXECUTE sp_xml_removedocument @iDoc

But this doent give a correct answer. On the second row that doesnt fine MoneyLine it repeats the first row values. enter image description here

The values marked with circle showld be empty.Any help please?

can you make the output readable/view-able/larger?

gloomy.penguin
– gloomy.penguin

2013-10-26 08:17:38 +00:00
Commented Oct 26, 2013 at 8:17 — gloomy.penguin
– gloomy.penguin, Commented Oct 26, 2013 at 8:17
I croped a bit the picture

user1361208
– user1361208

2013-10-26 08:23:20 +00:00
Commented Oct 26, 2013 at 8:23 — user1361208
– user1361208, Commented Oct 26, 2013 at 8:23

Efran Cobisi · Accepted Answer · 2013-10-26 08:57:03Z

3

The XPath expressions you are using are not matching the Event element you are after. Considering only the 1, X and 2 columns, their XPath expressions should be:

[1] [varchar](5) 'MoneyLine/@Home',
[X] [varchar](5) 'MoneyLine/@Draw',
[2] [varchar](5) 'MoneyLine/@Away',

This way, they will be relative to the Event element you are considering. With your original expressions, they pointed to the first Event element of the parent of the row being processed and this is why they behaved wrongly. The same considerations apply for every other expression in your block of code starting with ../Event.

answered Oct 26, 2013 at 8:57

Efran Cobisi

6,5141 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1361208 Over a year ago

Can you help with my new xml?

roman · Accepted Answer · 2013-10-26 12:29:04Z

2

I suggest to use nodes() method for reading xml in SQL server, it's much easier to use. For your XML it could be something like this:

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
from @x.nodes('Events/Event') as T(C)

sql fiddle demo

To get data from element filtered by attribute, you can use xpath:

select
    T.C.value('(Participants/Participant[@Name="Odd"]/Odds)[1]/@OddsValue', 'float') as Odd,
    T.C.value('(Participants/Participant[@Name="Even"]/Odds)[1]/@OddsValue', 'float') as Even
from @x.nodes('Events/Event') as T(C)

sql fiddle demo

edited Oct 26, 2013 at 12:29

answered Oct 26, 2013 at 11:53

roman

118k30 gold badges205 silver badges209 bronze badges

2 Comments

user1361208 Over a year ago

Yes this works ok but if you see my second answer when i add a different node i cannot get all the parameters there. Please check my second answer

roman Over a year ago

@user1361208 dont' know what exactly you want. Updated answer to show how can you filter xml by attribute value. Answered your other question with another way of parsing - all participants to the rows

Bogdan Sahlean · Accepted Answer · 2013-10-26 09:13:09Z

Apart of Efran's answer (+1) I would use nodes and value methods (ref) to query XML:

SELECT  x.XmlCol.value('(@ID)[1]', 'INT') AS [ID],
        x.XmlCol.value('(@DateTimeGMT)[1]', '[varchar](100)') AS [DateTimeGMT],
        x.XmlCol.value('(Sport)[1]', '[varchar](100)') AS [Sport],
        -- ...
        x.XmlCol.value('(Participants/Participant1/@Name)[1]', '[varchar](100)') AS [Home],
        x.XmlCol.value('(Participants/Participant2/@Name)[1]', '[varchar](100)') AS [Away],
        -- ...
        x.XmlCol.value('(MoneyLine/@Home)[1]', '[varchar](5)') AS [1],
        x.XmlCol.value('(MoneyLine/@Draw)[1]', '[varchar](5)') AS [X],
        x.XmlCol.value('(MoneyLine/@Away)[1]', '[varchar](5)') AS [2]
FROM    @x.nodes('/Events/Event') x(XmlCol);

Collectives™ on Stack Overflow

XMl parse in SQL Server

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related