SQL Server 2005 For XML Explicit - Need help formatting *

Question

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.

Answer

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

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/2811/" >SQL Server 2005 For XML Explicit - Need help formatting< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.