Menu
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.
530
A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
To create a SQL table using XML elements, all you have to do is to change the mode value of the OPENXML function to 2 and change the name of the attributes to the name of the element you want to retrieve.
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
