flattening xml data in sql server

I have the following xml data in a xml column (not typed) called xml_response

<SEIContent>    
<Request>
<eq:Charge>
  <v:Type>MaintCharge</v:Type>
  <v:Term>0</v:Term>
  <v:StartMonth>0</v:StartMonth>
  <v:EndMonth>0</v:EndMonth>
  <v:Rate>0</v:Rate>
  <v:RebateRatio>0</v:RebateRatio>
  <v:MaxRebate>0</v:MaxRebate>
  <v:TieredCharge>
    <v:Term>0</v:Term>
    <v:Rate>0.75</v:Rate>
    <v:LowerBand>0</v:LowerBand>
    <v:UpperBand>249999.99</v:UpperBand>
    <v:BandCurrency>GBP</v:BandCurrency>
  </v:TieredCharge>
  <v:TieredCharge>
    <v:Term>0</v:Term>
    <v:Rate>0.7</v:Rate>
    <v:LowerBand>250000</v:LowerBand>
    <v:UpperBand>499999.99</v:UpperBand>
    <v:BandCurrency>GBP</v:BandCurrency>
  </v:TieredCharge>
  <v:TieredCharge>
    <v:Term>0</v:Term>
    <v:Rate>0.6</v:Rate>
    <v:LowerBand>500000</v:LowerBand>
    <v:UpperBand>999999.99</v:UpperBand>
    <v:BandCurrency>GBP</v:BandCurrency>
  </v:TieredCharge>
  <v:TieredCharge>
    <v:Term>0</v:Term>
    <v:Rate>0.5</v:Rate>
    <v:LowerBand>1000000</v:LowerBand>
    <v:UpperBand>9999999.99</v:UpperBand>
    <v:BandCurrency>GBP</v:BandCurrency>
  </v:TieredCharge>
</eq:Charge>  
 <eq:Charge>
  <v:Type>MaintCharge</v:Type>
  <v:Term>0</v:Term>
  <v:StartMonth>0</v:StartMonth>
  <v:EndMonth>59</v:EndMonth>
  <v:Rate>1.5</v:Rate>
  <v:RebateRatio>0</v:RebateRatio>
  <v:MaxRebate>0</v:MaxRebate>
</eq:Charge>
<eq:Charge>
  <v:Type>MaintCharge</v:Type>
  <v:Term>0</v:Term>
  <v:StartMonth>60</v:StartMonth>
  <v:EndMonth>0</v:EndMonth>
  <v:Rate>0.5</v:Rate>
  <v:RebateRatio>0</v:RebateRatio>
  <v:MaxRebate>0</v:MaxRebate>
</eq:Charge>
<eq:Charge>
  <v:Type>QAC</v:Type>
  <v:Basis>FixedAmount</v:Basis>
  <v:Term>0</v:Term>
  <v:StartMonth>0</v:StartMonth>
  <v:EndMonth>0</v:EndMonth>
  <v:Rate>105</v:Rate>
  <v:RebateRatio>0</v:RebateRatio>
  <v:MaxRebate>0</v:MaxRebate>
</eq:Charge>
</Request>
<Response>
  <eq:Ref>QV00000393</eq:Ref>
</Response>
</SEIContent>

So you'll notice that some charges contain a repeating element TieredCharge and some don't

I've written the following query:

WITH XMLNAMESPACES('http://lu/blah' AS eq, 'http://lu/blah2' AS v,
  DEFAULT 'http://lu/blah3'
 SELECT
 nref.value('Response[1]/eqRef[1]', 'nvarchar(200)') Ref,
 ncharge.value('v:Type[1]', 'nvarchar(50)') ChargeType,
 ncharge.value('v:Basis[1]', 'nvarchar(50)') ChargeBasis,
 ncharge.value('v:Term[1]', 'int') Term,
 ncharge.value('v:StartMonth[1]', 'int') StartMonth,
 ncharge.value('v:EndMonth[1]', 'int') EndMonth,
 ncharge.value('v:Rate[1]', 'money') Rate,
 ncharge.value('v:RebateRatio[1]', 'money') RebateRatio,
 ncharge.value('v:MaxRebate[1]', 'money') MaxRebate,
 tcharge.value('v:Term[1]', 'int') TieredTerm,
 tcharge.value('v:Rate[1]', 'money') TieredRate,
 tcharge.value('v:LowerBand[1]', 'money') TieredLowerBand,
 tcharge.value('v:UpperBand[1]', 'money') TieredUpperBand
 INTO #TempCharges
 FROM xml_response CROSS APPLY response_body.nodes('//SEIContent') AS Quote(nref) 
 CROSS APPLY response_bod开发者_开发问答y.nodes('//Request//eq:Charge') AS Charge(ncharge) 
 CROSS APPLY response_body.nodes('//Request//eq:Charge//v:TieredCharge') AS TieredCharge(tcharge)
 WHERE nref.value('Request[1]/eq:Product[1]', 'nvarchar(60)') <> 'AVL'

 select * from #TempCharges

So although this flattens the xml, what I'm getting is repeated rows even if the eq:Charge element doesn't contain a v:TieredCharge repeating element?

For example I get 4 rows where the charge type is QAC even though there is only one element with that type?

How do I query this so that I only get one row for each Charge element, unless there are repeating child elements below it, in which case I'll only get the rows repeated for each element?

So I should get something like this:

 MaintChange TieredCharge1 row
 MaintChange TieredCharge2 row
 MaintChange TieredCharge3 row
 MaintChange TieredCharge4 row
 MaintCharge
 MaintCharge
 QAC

---

So the trick is to use OUTER APPLY in the last bit of the query - its kind of like an inner join. Then it works.