sql-server - SQL Server 中的 XML 解析

Question

我有这个 xml 结构。我使用以下 sql 来读取值，但它不起作用

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

但这并没有给出正确的答案。在不完善 MoneyLine 的第二行，它重复第一行的值。

标有圆圈的值显示为空。有什么帮助吗？

Answer 1

您正在使用的 XPath 表达式与您之后的 Event 元素不匹配。仅考虑 1、X 和 2 列，它们的 XPath 表达式应该是:

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

这样，它们将与您正在考虑的 Event 元素相关联。使用您的原始表达式，它们指向正在处理的行的父级的第一个 Event 元素，这就是它们行为错误的原因。同样的注意事项适用于以 ../Event 开头的代码块中的所有其他表达式。