title: SQL Server XML query values to columns
body: <p>I'm trying to shred a table of XML documents into SQL Server columns and am going around in circles with one bit.  </p>
  
  <p>Basically I have a table (<code>ID int, XMLData XML</code>) and each row will contain a document in the XML column.</p>
  
  <p>I need to turn it into the following format</p>
  
  <pre><code>(
  ID int, 
  ReferenceCurrency varchar, 
  TargetCurrency varchar, 
  ReferenceAmount decimal, 
  TargetAmount decimal, 
  DueDate date
  )
  </code></pre>
  
  <p>I've cut down this table.</p>
  
  <p>The code I have inherited was a mix of XML.value and string searches with charindex which haven't been too reliable.</p>
  
  <p>This is a snippet of the message we have.</p>
  
  <pre><code>&lt;ReferenceCurrency&gt;
        &lt;Ccy&gt;GBP&lt;/Ccy&gt;
  &lt;/ReferenceCurrency&gt;
  &lt;TargetCurrency&gt;
        &lt;Ccy&gt;USD&lt;/Ccy&gt;
  &lt;/TargetCurrency&gt;    
  &lt;BalanceAmtItem Type="technical_account_settlement_balance_due_to_sender"&gt;
        &lt;Amt Ccy="USD" CcyIndic="reference_currency" Share="receiver_share"&gt;65.62&lt;/Amt&gt;
        &lt;Amt Ccy="USD" CcyIndic="target_currency" Share="receiver_share"&gt;96.62&lt;/Amt&gt;
        &lt;DueDate&gt;2019-09-04&lt;/DueDate&gt;
  &lt;/BalanceAmtItem&gt;
  </code></pre>
  
  <p>I can get most of the data just using XML.value </p>
  
  <pre><code>SELECT 
      ID, 
      XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/ReferenceCurrency)[1]', 'varchar(4)') AS ReferenceCurrency ,
      XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/TargetCurrency)[1]', 'varchar(4)') AS TargetCurrency,
      XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/BalanceAmtItem/DueDate)[1]', 'date') AS DueDate
  FROM
      dta
  </code></pre>
  
  <p>The bit I'm struggling with is how to get the two amount columns where CcyIndic = reference_currency or target_currency.</p>
  
  <p>In this example I would expect to get a single line of 1, GBP, USD, 65.62, 96.62, 2019-09-04</p>
  
  <p>Any help appreciated.</p>