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

Data and Databases
1

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!

2

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.

3

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:

4

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.