'SQL Query XML elements displayed in one row

I am new to XML (project thrown into my lap last week) and have been hitting a wall with the following situation:

I am attempting to run the following (simplified table values):

select  ID as [@ID],v1,v2,v3,v4,
        (select V7
         from xml_b r
         where m.id=r.id
         for xml path (''), type) as v7_values,v5,v6
    from #a m
    where id=1
    order by [ID]
for xml path('values'), root('data')

The output is:

<data>
  <values ID="1">
    <v1>1</v1>
    <v2>2</v2>
    <v3>3</v3>
    <v4>4</v4>
    <v7_values>
      <V7>1</V7>
      <V7>1</V7>
      <V7>1</V7>
      <V7>1</V7>
      <V7>1</V7>
    </v7_values>
    <v5>5</v5>
    <v6>6</v6>
  </values>
</data>

I am needing the v7 elements (nodes?) all in one row. Currently the process is to do this manually and I'd rather not and just have it accomplished using SQL. The desired output:

<data>
  <values ID="1">
    <v1>1</v1>
    <v2>2</v2>
    <v3>3</v3>
    <v4>4</v4>
    <v7_values>
      <V7>1</V7> <V7>2</V7><V7>3</V7><V7>4</V7><V7>5</V7>
    </v7_values>
    <v5>5</v5>
    <v6>6</v6>
  </values>
</data>

I tried inserting FOR XML AUTO but received the following:

<v7_values>&lt;r V7="1"/&gt;&lt;r V7="1"/&gt;&lt;r V7="1"/&gt;&lt;r V7="1"/&gt;&lt;r V7="1"/&gt;</v7_values>

I've tried Stack Overflow for an existing solution but have not been able to successfully find a solution. Any help will be greatly appreciated

Thanks,

Mike



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source