0

Dear all,
I have this query to generate sql row to XML Mode.

SELECT CurrencyID, CurrencyName FROM MasterCurrency ORDER BY CurrencyID FOR XML RAW ('Currency') , ELEMENTS;

the result is :

<Currency>
  <CurrencyID>AUD</CurrencyID>
  <CurrencyName>AUSTRALIAN DOLLAR</CurrencyName>
</Currency>
<Currency>
  <CurrencyID>CHF</CurrencyID>
  <CurrencyName>SWISS FRANC</CurrencyName>
</Currency>
<Currency>
  <CurrencyID>EUR</CurrencyID>
  <CurrencyName>EUROPEAN EURO</CurrencyName>
</Currency>

I want the result is like this:

<currency>
	<Currency>
		<CurrencyID>AUD</CurrencyID>
		<CurrencyName>AUSTRALIAN DOLLAR</CurrencyName>
	</Currency>
	<Currency>
		<CurrencyID>CHF</CurrencyID>
		<CurrencyName>SWISS FRANC</CurrencyName>
	</Currency>
	<Currency>
		<CurrencyID>EUR</CurrencyID>
		<CurrencyName>EUROPEAN EURO</CurrencyName>
	</Currency> 
<currency>

using old format, XML data cannot be loaded into dataset,
I found this error :

There are multiple root elements. Line 8, position 3.

Thanks :beer:

1
Contributor
1
Reply
3
Views
7 Years
Discussion Span
Last Post by Kusno
0

I have found how to do it.

SELECT CurrencyID, CurrencyName FROM MasterCurrency ORDER BY CurrencyID FOR XML RAW ('Currency') , ROOT('Master'), ELEMENTS;

Thanks anyway

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.