title: Convert columns to xml collection with attributes
body: <p>I would like to have a row for each row in the table, but transform the columns to an xml collection as efficiently as possible.  In the example below it is a flattened table - but in the real world the columns would require many joins to get - resulting in many reads.</p>
  
  <p>For example:</p>
  
  <pre><code>declare @tbl table (
   Id int identity (1, 1) primary key
  ,PolicyNumber   varchar(100) not null
  ,InsuredName    varchar(100) not null
  ,EffectiveDate  datetime2 not null
  ,Premium        numeric(22, 7)
  )
  
  insert into @tbl (PolicyNumber, InsuredName, EffectiveDate, Premium)
  values ('2017A-ALKJ02', 'Insured Number 1', '2017-01-01', 1000)
    ,('2017A-BSDSDFWEF2', 'Insured Number 2', '2017-06-01', 2000)
  
  select Id
    ,(select [@name] = 'PolicyNumber', [@type] = 'string', [text()] = PolicyNumber from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
    ,(select [@name] = 'InsuredName', [@type] = 'string', [text()] = [inner].InsuredName from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
    ,(select [@name] = 'EffectiveDate', [@type] = 'datetime', [text()] = [inner].EffectiveDate from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
    ,(select [@name] = 'Premium', [@type] = 'numeric', [text()] = [inner].Premium from @tbl [inner] where [inner].Id = [outer].Id for xml path ('dt'))
  from @tbl [outer]
  </code></pre>
  
  <p>Yields the individual columns in their own xml element, but I am after each row to have it's primary key and the structure:</p>
  
  <pre><code>&lt;dts&gt;
    &lt;dt name="PolicyNumber" type="string"&gt;2017A-ALKJ02&lt;/dt&gt;
    &lt;dt name="InsuredName" type="string"&gt;Insured Number 1&lt;/dt&gt;
    &lt;dt name="EffectiveDate" type="datetime"&gt;2017-01-01T00:00:00&lt;/dt&gt;
    &lt;dt name="Premium" type="numeric"&gt;1000.0000000&lt;/dt&gt;
  &lt;/dts&gt;
  </code></pre>
  
  <p>I understand this can be achieved with many sub-queries, but does anyone know of an easy way to have a single query that is smart enough to have the PK and all the individual columns transformed into an element in the dts collection?</p>