从XML列返回多个类别值

Question

`<cassettes>
 <cassette>
    <cassetteId>A</cassetteId>
    <capacity>290</capacity>
    <denominations>
      <denomination>
        <value>1</value>
        <currency>US</currency>
        <notesAvailable>398</notesAvailable>
        <notesToDispense>24</notesToDispense>
        <notesDispensed>24</notesDispensed>
        <notesDeposited>0</notesDeposited>
        <notesSuggested>398</notesSuggested>
      </denomination>
    </denominations>
    <status>0</status>
    <position>0</position>
  </cassette>...`

上面是xml列值的摘录。有问题的数量的节点是"notesdispensesed".There是12个cassetteId的A-K。每盒磁带都有一种面额。例如:盒式磁带A和B包含1，盒式磁带C包含5，盒式磁带D&E包含10，等等。

下面的select stmt为cassetteId A返回值24。我正在努力避免为每个磁带写出12次。有没有办法根据面值返还配给金额？任何解决方案都非常受欢迎。

Select column.value('(/cassettes/cassette/denominations/denomination/notesDispensed)[1]','varchar(max)') 

Answer 1

这看起来像您使用sql:variable要找的东西吗？

DECLARE @iterator = 1

SELECT 
    Child.value('(/cassettes/cassette/denominations/denomination/notesDispensed)[1]', 'varchar(max)'),
FROM
    XMLField.nodes("/cassettes/denomination[sql:variable("@iterator")]/ChildNode") AS N(Child)