Ad
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.
Ad
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
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad