和 1:n 处有 1:n 数据 数据位于 .您需要通过 APPLY 使用 .nodes() 两次.

There is 1:n data at <Margin> and again 1:n data at <MarginRevenue>. You need to use .nodes() twice via APPLY.

declare @xml xml = '<Margins>
    <Margin type="type1" currencyCode="currencyCode1">
      <MarginRevenue>1.1</MarginRevenue>
      <MarginRevenue>1.2</MarginRevenue>
      <MarginRevenue>1.3</MarginRevenue>
    </Margin>
    <Margin type="type2" currencyCode="currencyCode2">
      <MarginRevenue>1.4</MarginRevenue>
      <MarginRevenue>1.5</MarginRevenue>
      <MarginRevenue>1.6</MarginRevenue>
    </Margin>
    <Margin type="type3" currencyCode="currencyCode3">
      <MarginRevenue>1.7</MarginRevenue>
      <MarginRevenue>1.8</MarginRevenue>
      <MarginRevenue>1.9</MarginRevenue>
      </Margin>
    </Margins>'

SELECT
 [Margin_Type]         = Marg.value('@type', 'varchar(100)') 
,[Margin_currencyCode] = Marg.value('@currencyCode', 'varchar(100)')
,[Revenue_Value]       = Rev.value('text()[1]','decimal(15,5)') 
FROM
@xml.nodes('Margins/Margin') AS A(Marg)
OUTER APPLY Marg.nodes('MarginRevenue') B(Rev);

结果

Type    currencyCode    Revenue_Value
-------------------------------------
type1   currencyCode1   1.10000
type1   currencyCode1   1.20000
type1   currencyCode1   1.30000
type2   currencyCode2   1.40000
type2   currencyCode2   1.50000
type2   currencyCode2   1.60000
type3   currencyCode3   1.70000
type3   currencyCode3   1.80000
type3   currencyCode3   1.90000