title: TSQL Reading an XML File based on Element Attributes
body: <p>I need help with extracting the data from an XML file I’ve been sent (and have no control over), normally not any issue but the way this file is structured has me confused.  Sample XML below. None of the articles I’ve searched have a solution that seems to work with this XML file in a way I need</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes" ?&gt; 
  &lt;Entities TotalResults="3"&gt;
  &lt;Entity Type="testing"&gt;&lt;Fields&gt;
  &lt;Field Name="type"&gt;&lt;Value&gt;Internal&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="name"&gt;&lt;Value&gt;Customer123&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="updated"&gt;&lt;Value&gt;Yes&lt;/Value&gt;&lt;/Field&gt;
  &lt;/Fields&gt;&lt;/Entity&gt;  
  &lt;Entity Type="testing"&gt;&lt;Fields&gt;
  &lt;Field Name="type"&gt;&lt;Value&gt;External&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="name"&gt;&lt;Value&gt;CustomerABC&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="updated"&gt;&lt;Value&gt;Yes&lt;/Value&gt;&lt;/Field&gt;
  &lt;/Fields&gt;&lt;/Entity&gt;
  &lt;Entity Type="testing"&gt;&lt;Fields&gt;
  &lt;Field Name="type"&gt;&lt;Value&gt;Unknown&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="name"&gt;&lt;Value&gt;Customer456&lt;/Value&gt;&lt;/Field&gt;
  &lt;Field Name="updated"&gt;&lt;Value&gt;No&lt;/Value&gt;&lt;/Field&gt;
  &lt;/Fields&gt;&lt;/Entity&gt;
  &lt;/Entities&gt;
  </code></pre>
  
  <p>What I want is</p>
  
  <p>Entity  EntityType  Name            Updated</p>
  
  <p>1   Internal    Customer123  Yes</p>
  
  <p>2   External    CustomerABC  Yes</p>
  
  <p>3   Unknown         Customer456  No</p>
  
  <p>I can get the data from the file but not in the format I need..</p>
  
  <pre><code>DECLARE @input XML
  SELECT @input = CAST(x AS XML)FROM OPENROWSET(BULK 'C:\Test.xml', SINGLE_BLOB) AS T(x)
  
  
   SELECT T1.steps.value('Value[1]', 'nvarchar(Max)') As EntityType
     FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
     CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
     Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'type'
  
  
     SELECT 
     T1.steps.value('Value[1]', 'nvarchar(Max)') As Name
     FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
     CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
     Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'name'
  
     SELECT 
     T1.steps.value('Value[1]', 'nvarchar(Max)') As Updated
     FROM @input.nodes('Entities/Entity/Fields/Field') AS Tbl(Scan)
     CROSS APPLY Tbl.Scan.nodes('.') as T1(steps)       
     Where  Tbl.Scan.value('(@Name)[1]', 'varchar(max)') = 'updated'
  </code></pre>
  
  <p>Which gives me three separate tables with 3 rows each, is it possible to run a query which will return data as I need? </p>