Member Avatar for Kusno

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
  • 99 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by Kusno
Member Avatar for Kusno

I have found how to do it.

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

Thanks anyway

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.