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?

---

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.

---

add ELEMENTS to the end:

SELECT AccountNumber FROM Account FOR XML AUTO, ELEMENTS