SQL Server 2005 For XML Explicit - Need help formatting

SQL Server 2005 For XML Explicit - Need help formatting

"I have a table with a structure like the following:

LocationID
AccountNumber

long-guid-here
12345

long-guid-here
54321

To pass into another stored procedure, I need the XML to look like this:
<root>
<clientID>12345</clientID>
<clientID>54321</clientID>
</root>

The best I've been able to do so far was getting it like this:
<root clientID=""10705""/>

I'm using this SQL statement:
SELECT
1 as tag,
null as parent,
AccountNumber as 'root!1!clientID'
FROM
Location.LocationMDAccount
WHERE
locationid = 'long-guid-here'
FOR XML EXPLICIT

So far, I've looked at the documentation on the MSDN page, but I've not come out with the desired results.

@KG,
Yours gave me this output actually:
<root>
<Location.LocationMDAccount>
<clientId>10705</clientId>
</Location.LocationMDAccount>
</root>

I'm going to stick with the FOR XML EXPLICIT from Chris Leon for now."

Asked by: Guest | Views: 399

Total answers/comments: 4

Comments display order:

Guest [Entry]

"try

SELECT
1 AS Tag,
0 AS Parent,
AccountNumber AS [Root!1!AccountNumber!element]
FROM
Location.LocationMDAccount
WHERE
LocationID = 'long-guid-here'
FOR XML EXPLICIT"

Guest [Entry]

"Try this, Chris:

SELECT
AccountNumber as [clientId]
FROM
Location.Location root
WHERE
LocationId = 'long-guid-here'
FOR
XML AUTO, ELEMENTS

TERRIBLY SORRY! I mixed up what you were asking for. I prefer the XML AUTO just for ease of maintainance, but I believe either one is effective. My apologies for the oversight ;-)"

Guest [Entry]

"Using SQL Server 2005 (or presumably 2008) I find for XML PATH to allow for much easier to maintain SQL than for XML Explicit (particularly once the SQL is longer).

In this case:

SELECT AccountNumber as ""clientID""
FROM Location.LocationMDAccount
WHERE locationid = 'long-guid-here'
FOR XML PATH (''), Root ('root');"

Guest [Entry]

"I got it with:

select
1 as tag,
null as parent,
AccountNumber as 'root!1!clientID!element'
from
Location.LocationMDAccount
where
locationid = 'long-guid-here'
for xml explicit"