MS SQL XML -按属性访问行集

Question

我试图访问带有属性的行集，但根本找不到解决方案。有更多行集的特殊特性，它们仅根据属性或值的不同而不同

在这个样本中

<rowset name=skills.....>

XML：

    <result>
      <characterID>118715987</characterID>
      <name>Tom Dalton</name>
      <homeStationID>60008653</homeStationID>
      <DoB>2006-12-12 15:34:00</DoB>
      <race>Caldari</race>
      <bloodLine>Deteis</bloodLine>
      <ancestry>Merchandisers</ancestry>
      <gender>Male</gender>
      <corporationName>WEYLAND-YUTANI Inc.</corporationName>
      <corporationID>448087618</corporationID>
      <allianceName />
      <allianceID>0</allianceID>
      <factionName />
      <factionID>0</factionID>
      <cloneTypeID>164</cloneTypeID>
      <cloneName>Clone Grade Alpha</cloneName>
      <cloneSkillPoints>0</cloneSkillPoints>
      <freeSkillPoints>0</freeSkillPoints>
      <freeRespecs>0</freeRespecs>
      <cloneJumpDate>2015-02-23 15:39:43</cloneJumpDate>
      <lastRespecDate>2012-09-28 19:33:39</lastRespecDate>
      <lastTimedRespec>2012-09-06 12:21:23</lastTimedRespec>
      <remoteStationDate>0001-01-01 00:00:00</remoteStationDate>
      <rowset name="jumpClones" key="jumpCloneID" columns="jumpCloneID,typeID,locationID,cloneName">
        <row jumpCloneID="21434822" typeID="164" locationID="60011866" cloneName="" />
        <row jumpCloneID="16105277" typeID="164" locationID="60014911" cloneName="" />
      </rowset>
      <rowset name="jumpCloneImplants" key="jumpCloneID" columns="jumpCloneID,typeID,typeName">
        <row jumpCloneID="21434822" typeID="2082" typeName="Genolution Core Augmentation CA-1" />
        <row jumpCloneID="21434822" typeID="2589" typeName="Genolution Core Augmentation CA-2" />
        <row jumpCloneID="21434822" typeID="3192" typeName="Eifyr and Co. 'Gunslinger' Motion Prediction MR-706" />
        <row jumpCloneID="21434822" typeID="3195" typeName="Eifyr and Co. 'Gunslinger' Surgical Strike SS-906" />
        <row jumpCloneID="21434822" typeID="3215" typeName="Inherent Implants 'Lancer' Large Energy Turret LE-1006" />
        <row jumpCloneID="21434822" typeID="3239" typeName="Inherent Implants 'Squire' Capacitor Management EM-806" />
        <row jumpCloneID="21434822" typeID="3246" typeName="Inherent Implants 'Squire' Capacitor Systems Operation EO-606" />
        <row jumpCloneID="21434822" typeID="10225" typeName="Social Adaptation Chip - Standard" />
        <row jumpCloneID="21434822" typeID="33393" typeName="Genolution Core Augmentation CA-3" />
        <row jumpCloneID="21434822" typeID="33394" typeName="Genolution Core Augmentation CA-4" />
      </rowset>
      <jumpActivation>0001-01-01 00:00:00</jumpActivation>
      <jumpFatigue>0001-01-01 00:00:00</jumpFatigue>
      <jumpLastUpdate>0001-01-01 00:00:00</jumpLastUpdate>
      <balance>272039.14</balance>
      <rowset name="implants" key="typeID" columns="typeID,typeName">
        <row typeID="10208" typeName="Memory Augmentation - Standard" />
        <row typeID="10212" typeName="Neural Boost - Standard" />
        <row typeID="10216" typeName="Ocular Filter - Standard" />
        <row typeID="10221" typeName="Cybernetic Subprocessor - Standard" />
        <row typeID="10225" typeName="Social Adaptation Chip - Standard" />
        <row typeID="3265" typeName="Zainou 'Gypsy' CPU Management EE-602" />
        <row typeID="22570" typeName="Inherent Implants 'Yeti' Ice Harvesting IH-1003" />
      </rowset>
      <attributes>
        <intelligence>21</intelligence>
        <memory>17</memory>
        <charisma>17</charisma>
        <perception>27</perception>
        <willpower>17</willpower>
      </attributes>
      <rowset name="skills" key="typeID" columns="typeID,skillpoints,level,published">
        <row typeID="3416" skillpoints="256000" level="5" published="1" />
        <row typeID="3426" skillpoints="256000" level="5" published="1" />
        <row typeID="3432" skillpoints="512000" level="5" published="1" />
        </rowset>
      <rowset name="certificates" key="certificateID" columns="certificateID" />
      <rowset name="corporationRoles" key="roleID" columns="roleID,roleName">
        <row roleID="1" roleName="roleDirector" />
      </rowset>
      <rowset name="corporationRolesAtHQ" key="roleID" columns="roleID,roleName">
        <row roleID="1" roleName="roleDirector" />
      </rowset>
      <rowset name="corporationRolesAtBase" key="roleID" columns="roleID,roleName">
        <row roleID="1" roleName="roleDirector" />
      </rowset>

  <rowset name="corporationRolesAtOther" key="roleID" columns="roleID,roleName">
    <row roleID="1" roleName="roleDirector" />
  </rowset>
  <rowset name="corporationTitles" key="titleID" columns="titleID,titleName" />
</result>

TSQL：

SELECT
  @characterID AS characterID,
  a.b.value('@typeID', 'int') AS typeID,
  b.value('@skillpoints', 'int') AS skillpoints,
  b.value('@level', 'int') AS [level],
  b.value('@published', 'int') AS published
FROM
  #xml x
  CROSS APPLY x.yourXML.nodes('/eveapi/result/rowset[name=skills]/row') a(b);

但是结果是空的:'( /eveapi/结果是正确的。原始XML要大得多。

你们中有谁有主意或解决办法吗？

Answer 1

您只是忽略了属性值“技能”周围的双引号。以下是一些简化的代码：

select  b.value('./@typeID', 'int') AS typeID,
        b.value('./@skillpoints', 'int') AS skillpoints,
        b.value('./@level', 'int') AS [level],
        b.value('./@published', 'int') AS published
    from #xml x
    cross apply x.yourXML.nodes('result/rowset[@name="skills"]/row') a(b);