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.
try
Try this, Chris:
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 ;-)
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:
I got it with: