Q:

Producing XML with NodeValues from SQL Server

I'm trying to produce XML from SQL Server, and need the field I'm selecting to be in the nodevalue rather than an attribute.
My current SQL is:-

SELECT AccountNumber FROM Account FOR XML AUTO

which then produces XML like:-
<Account AccountNumber="12345" />

where I'd like it to appear as:-
<AccountNumber>12345</AccountNumber>

Can anyone point me in the right direction?

A:

You should also check out the FOR XML PATH(...),ROOT(....) construct available in SQL Server 2008 and newer: MSDN docs
This would allow you to 

specify a root node for a number of XML elements
specify the XML tag names
specify certain bits as XML attributes

So in your case, you could e.g. create something like:
SELECT
    AccountNumber, AcctID AS '@AcctID',
    AccountHolder
FROM
    dbo.Accounts
FOR XML PATH('Account'), ROOT('AllAccounts')

and you would get something like this as your output:
<AllAccounts>
   <Account AcctID="42">
      <AccountNumber>12345</AccountNumber>
      <AccountHolder>John Doe</AccountHolder>
   </Account> 
   <Account AcctID="4711">
      <AccountNumber>54321</AccountNumber>
      <AccountHolder>Jane Willis-Doe</AccountHolder>
   </Account> 
   ..... (possibly more <Account> elements) ......
</AllAccounts>

With the FOR XML PATH method, you can very easily fine-tune what your resulting XML should look like.

