How to structure XML output in SQL Server 2005 using FOR XML EXPLICIT?

I’m working with SQL Server 2005 and need help formatting XML output. My table looks like this:

LocationID      AccountNumber
------------    -------------
guid1           12345
guid2           54321

I want to create XML that looks like:

<root> 
    <clientID>12345</clientID>
    <clientID>54321</clientID>
</root>

Right now, my query produces:

<root clientID="10705"/>

Here’s my current SQL:

SELECT
    1 as tag,
    null as parent,
    AccountNumber as 'root!1!clientID'
FROM
    CustomerAccounts
WHERE
    locationid = 'some-guid'
FOR XML EXPLICIT

I’ve checked the docs but can’t figure out how to get the structure I need. Any ideas on how to tweak this query to get the desired output? I’m open to other approaches too, but prefer sticking with FOR XML EXPLICIT if possible. Thanks!

I’ve encountered similar challenges with XML formatting in SQL Server. While FOR XML EXPLICIT offers fine-grained control, it can be tricky to get the exact structure you want. Have you considered using FOR XML PATH instead? It’s often simpler for these types of outputs. Here’s a query that should produce the XML structure you’re after:

SELECT AccountNumber AS 'clientID'
FROM CustomerAccounts
WHERE locationid = 'some-guid'
FOR XML PATH(''), ROOT('root')

This approach is more straightforward and easier to maintain. It generates each ‘clientID’ element separately and wraps them in a ‘root’ element. If you must stick with EXPLICIT, you’ll need to adjust your tag and parent values, but PATH is generally more intuitive for this kind of nested structure.

hmm, interesting challenge! have you considered using XQUERY? it’s pretty powerful for xml manipulation. something like:

SELECT (SELECT AccountNumber AS ‘
FROM CustomerAccounts
WHERE locationid = ‘some-guid’
FOR XML PATH(‘clientID’), TYPE).query('{
}’)
AS XMLResult

might give you what ur after. what do you think? :thinking:

have u tried using FOR XML PATH? it’s usually easier for this stuff. something like:

SELECT AccountNumber AS ‘clientID’
FROM CustomerAccounts
WHERE locationid = ‘some-guid’
FOR XML PATH(‘’), ROOT(‘root’)

might work better for u. gives cleaner xml without the hassle of EXPLICIT.