<p>I have a table with a structure like the following:</p>
<hr />
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>LocationID</th>
<th>AccountNumber</th>
</tr>
</thead>
<tbody>
<tr>
<td>long-guid-here</td>
<td>12345</td>
</tr>
<tr>
<td>long-guid-here</td>
<td>54321</td>
</tr>
</tbody>
</table>
</div>
<p>To pass into another stored procedure, I need the XML to look like this:</p>
<pre><code>&lt;root&gt; 
    &lt;clientID&gt;12345&lt;/clientID&gt;
    &lt;clientID&gt;54321&lt;/clientID&gt;
&lt;/root&gt;
</code></pre>
<p>The best I've been able to do so far was getting it like this:</p>
<pre><code>&lt;root clientID=&quot;10705&quot;/&gt;
</code></pre>
<p>I'm using this SQL statement:</p>
<pre><code>SELECT
    1 as tag,
    null as parent,
    AccountNumber as 'root!1!clientID'
FROM
    Location.LocationMDAccount
WHERE
    locationid = 'long-guid-here'
FOR XML EXPLICIT
</code></pre>
<p>So far, I've looked at the documentation on <a href="http://msdn.microsoft.com/en-us/library/ms345137.aspx" rel="nofollow noreferrer">the MSDN page</a>, but I've not come out with the desired results.</p>
<hr />
<p>@KG,</p>
<p>Yours gave me this output actually:</p>
<pre><code>&lt;root&gt;
  &lt;Location.LocationMDAccount&gt;
    &lt;clientId&gt;10705&lt;/clientId&gt;
  &lt;/Location.LocationMDAccount&gt;
&lt;/root&gt;
</code></pre>
<p>I'm going to stick with the <code>FOR XML EXPLICIT</code> from Chris Leon for now.</p>

[{'answer_id': 2825, 'author': 'Chris Leon', 'author_id': 289, 'author_profile': 'https://Stackoverflow.com/users/289', 'pm_score': 3, 'selected': True, 'text': "<p>try</p>\n\n<pre><code>SELECT\n    1 AS Tag,\n    0 AS Parent,\n    AccountNumber AS [Root!1!AccountNumber!element]\nFROM\n    Location.LocationMDAccount\nWHERE\n    LocationID = 'long-guid-here'\nFOR XML EXPLICIT\n</code></pre>\n"}, {'answer_id': 2832, 'author': 'karlgrz', 'author_id': 318, 'author_profile': 'https://Stackoverflow.com/users/318', 'pm_score': 0, 'selected': False, 'text': "<p>Try this, Chris:</p>\n\n<pre><code>SELECT\n    AccountNumber as [clientId]\nFROM\n    Location.Location root\nWHERE\n    LocationId = 'long-guid-here'\nFOR\n    XML AUTO, ELEMENTS\n</code></pre>\n\n<p>TERRIBLY SORRY! I mixed up what you were asking for. I prefer the XML AUTO just for ease of maintainance, but I believe either one is effective. My apologies for the oversight ;-)</p>\n"}, {'answer_id': 2834, 'author': 'Chris Benard', 'author_id': 448, 'author_profile': 'https://Stackoverflow.com/users/448', 'pm_score': 0, 'selected': False, 'text': "<p>I got it with:</p>\n\n<pre><code>select\n1 as tag,\nnull as parent,\nAccountNumber as 'root!1!clientID!element'\nfrom\nLocation.LocationMDAccount\nwhere\nlocationid = 'long-guid-here'\nfor xml explicit\n</code></pre>\n"}, {'answer_id': 13536, 'author': 'jeffreypriebe', 'author_id': 1592, 'author_profile': 'https://Stackoverflow.com/users/1592', 'pm_score': 0, 'selected': False, 'text': '<p>Using SQL Server 2005 (or presumably 2008) I find for XML PATH to allow for much easier to maintain SQL than for XML Explicit (particularly once the SQL is longer).</p>\n\n<p>In this case:</p>\n\n<pre><code>SELECT AccountNumber as "clientID"\nFROM Location.LocationMDAccount\nWHERE locationid = \'long-guid-here\'\nFOR XML PATH (\'\'), Root (\'root\');\n</code></pre>\n'}, {'answer_id': 7953073, 'author': 'Asher', 'author_id': 156470, 'author_profile': 'https://Stackoverflow.com/users/156470', 'pm_score': 0, 'selected': False, 'text': "<pre>\nSELECT 1             as tag,\n       null          as parent,\n       AccountNumber as 'clientID!1!!element'\nFROM Location.LocationMDAccount\nWHERE locationid = 'long-guid-here'\nFOR XML EXPLICIT, root('root')\n</pre>\n"}]
['https://Stackoverflow.com/questions/2811', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/448/']