sql-server - SQL Server XML 查询错误输出

Question

我是 XML 的新手，我有这个 xml 数据..

<?xml version="1.0"?>
<OMeS xmlns="pm/cnf_pte_msn.1.0.xsd">
  <PMSetup startTime="2015-10-21T00:00:00.000+00:00:00" interval="60">

    <PMMOResult>
      <MO>
        <baseId>12345</baseId>
        <localMoid>67890</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>1601</M8000C0>
        <M8000C1>1597</M8000C1>
        <M8000C2>4</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

    <PMMOResult>
      <MO>
        <baseId>678910</baseId>
        <localMoid>109876</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>860</M8000C0>
        <M8000C1>858</M8000C1>
        <M8000C2>2</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

    <PMMOResult>
      <MO>
        <baseId>111213</baseId>
        <localMoid>131211</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>3533</M8000C0>
        <M8000C1>3504</M8000C1>
        <M8000C2>29</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

  </PMSetup>
</OMeS>

现在我想查询它然后像这样插入到表结构中..

startTime | interval | baseId | localMoid | M8000C0 | M8000C1 | M8000C2 | M8000C3 | M8000C4 | M8000C5

但是我得到了错误的输出。请。帮助!这是我的示例查询代码:

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier1.value('@startTime', 'varchar(50)') as startTime,
Tier1.value('@interval', 'int') as interval,
Tier2.value('baseId[1]', 'varchar(50)') as baseId,
Tier2.value('localMoid[1]', 'varchar(50)') as localMoid,
Tier3.value('M8000C0[1]', 'float') as M8000C0,
Tier3.value('M8000C1[1]', 'float') as M8000C1,
Tier3.value('M8000C2[1]', 'float') as M8000C2,
Tier3.value('M8000C3[1]', 'float') as M8000C3,
Tier3.value('M8000C4[1]', 'float') as M8000C4,
Tier3.value('M8000C5[1]', 'float') as M8000C5
FROM 
@RawXML.nodes('/OMeS/PMSetup') as T1(Tier1)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult/MO') as T2(Tier2)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult/NE_1.0') as T3(Tier3)

Answer 1

您的问题不清楚正确的结果是什么，但我假设您希望每个 XML 元素一行 <PMMOResult> :

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier1.value('@startTime', 'varchar(50)') as startTime,
Tier1.value('@interval', 'int') as interval,
Tier2.value('((.)/MO/baseId)[1]', 'varchar(50)') as baseId,
Tier2.value('((.)/MO/localMoid)[1]', 'varchar(50)') as localMoid,
Tier2.value('((.)/NE_1.0/M8000C0)[1]', 'float') as M8000C0,
Tier2.value('((.)/NE_1.0/M8000C1)[1]', 'float') as M8000C1,
Tier2.value('((.)/NE_1.0/M8000C2)[1]', 'float') as M8000C2,
Tier2.value('((.)/NE_1.0/M8000C3)[1]', 'float') as M8000C3,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C4,
Tier2.value('((.)/NE_1.0/M8000C5)[1]', 'float') as M8000C5
FROM  @RawXML.nodes('/OMeS/PMSetup') as T1(Tier1)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult') as T2(Tier2);

LiveDemo

输出:

╔══════════════════════════════════╦══════════╦════════╦═══════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║            startTime             ║ interval ║ baseId ║ localMoid ║ M8000C0 ║ M8000C1 ║ M8000C2 ║ M8000C3 ║ M8000C4 ║ M8000C5 ║
╠══════════════════════════════════╬══════════╬════════╬═══════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║  12345 ║     67890 ║    1601 ║    1597 ║       4 ║       0 ║       0 ║    0    ║
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║ 678910 ║    109876 ║     860 ║     858 ║       2 ║       0 ║       0 ║    0    ║
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║ 111213 ║    131211 ║    3533 ║    3504 ║      29 ║       0 ║       0 ║    0    ║
╚══════════════════════════════════╩══════════╩════════╩═══════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝

编辑:

更简单，不需要CROSS APPLY :

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier2.value('../@startTime', 'varchar(50)') as startTime,
Tier2.value('../@interval', 'int') as interval,
Tier2.value('((.)/MO/baseId)[1]', 'varchar(50)') as baseId,
Tier2.value('((.)/MO/localMoid)[1]', 'varchar(50)') as localMoid,
Tier2.value('((.)/NE_1.0/M8000C0)[1]', 'float') as M8000C0,
Tier2.value('((.)/NE_1.0/M8000C1)[1]', 'float') as M8000C1,
Tier2.value('((.)/NE_1.0/M8000C2)[1]', 'float') as M8000C2,
Tier2.value('((.)/NE_1.0/M8000C3)[1]', 'float') as M8000C3,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C4,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C5
FROM @RawXML.nodes('/OMeS/PMSetup/PMMOResult') as T2(Tier2);

LiveDemo