title: Select specific element from XML with XML Namespace
body: <p>I have an XML file stored in a SQL table. Now I want to read z specific element value using SQL. </p>
  
  <p>I can read a specific element value from the XML if the XML does not have namespace element in it. I want to read XML file with namespace.</p>
  
  <p>Here is my XML file:</p>
  
  <pre><code>&lt;currency:Envelope xmlns="http://www.currency.com/eurofxref" xmlns:currency="http://www.currency.org/xml/2002-08-01"&gt;
   &lt;Cube&gt;
    &lt;Cube time="2016-11-04"&gt;
     &lt;Cube currency="USD" rate="1.1093" /&gt;
     &lt;Cube currency="JPY" rate="114.24" /&gt;
     &lt;Cube currency="BGN" rate="1.9558" /&gt;
     &lt;Cube currency="CZK" rate="27.021" /&gt;      
    &lt;/Cube&gt;
   &lt;/Cube&gt;
  &lt;/currency:Envelope&gt;
  </code></pre>
  
  <p>Here is the SQL code for reading the values:</p>
  
  <pre><code>CREATE TABLE XMLwithOpenXML
  (
      Id INT IDENTITY PRIMARY KEY,
      XMLData XML,
      LoadedDateTime DATETIME
  )
  
  INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
  SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
  FROM OPENROWSET(BULK 'E:\currency.xml', SINGLE_BLOB) AS x;
  
  SELECT XMLData.value('(Cube/Cube/Cube[@currency="USD"]/@rate)[1]','VARCHAR(MAX)') 
  FROM XMLwithOpenXML
  </code></pre>