Producing XML with NodeValues from SQL Server

Question

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?

marc_s · Accepted Answer · 2011-07-06 11:50:48Z

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.

Mladen Prajdic · Accepted Answer · 2011-07-06 11:30:41Z

2

add ELEMENTS to the end:

SELECT AccountNumber FROM Account FOR XML AUTO, ELEMENTS

answered Jul 6, 2011 at 11:30

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

Collectives™ on Stack Overflow

Producing XML with NodeValues from SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related