Home All advices
ENPL
Modify date: 2013-01-16 09:51:06

Export data from table into xml

Below are shown how export data from table into xml. The SQL Server supports three modes: RAW, AUTO and EXPLICIT. That provide different transformation semantics.

sample tables

ci_idci_nameci_population
1Berlin4262480
2Hamburg1735568
3Paris11840304
4Lyon1760456

"ROW"

"ROW" - Gives as the element name for each row

example of a solution

select ci_name, ci_population from cities for xml raw

result xml

<row ci_name="Berlin" ci_population="4262480" />
<row ci_name="Hamburg" ci_population="1735568" />
<row ci_name="Paris" ci_population="11840304" />
<row ci_name="Lyon" ci_population="1760456" />

"ROW" - Gives as the element name for each row in text nodes

example of a solution

select ci_name, ci_population from cities for xml raw, elements

result xml

<row>
  <ci_name>Berlin</ci_name>
  <ci_population>4262480</ci_population>
</row>
<row>
  <ci_name>Hamburg</ci_name>
  <ci_population>1735568</ci_population>
</row>
<row>
  <ci_name>Paris</ci_name>
  <ci_population>11840304</ci_population>
</row>
<row>
  <ci_name>Lyon</ci_name>
  <ci_population>1760456</ci_population>
</row>

"AUTO"

"AUTO" - Gives Table Name as the element name for each Row

example of a solution

select ci_name, ci_population from cities for xml auto

result xml

<cities ci_name="Berlin" ci_population="4262480" />
<cities ci_name="Hamburg" ci_population="1735568" />
<cities ci_name="Paris" ci_population="11840304" />
<cities ci_name="Lyon" ci_population="1760456" />

"AUTO" - Gives Table Name as the element name for each Row in text nodes

example of a solution

select ci_name, ci_population from cities for xml auto

result xml

<cities>
  <ci_name>Berlin</ci_name>
  <ci_population>4262480</ci_population>
</cities>
<cities>
  <ci_name>Hamburg</ci_name>
  <ci_population>1735568</ci_population>
</cities>
<cities>
  <ci_name>Paris</ci_name>
  <ci_population>11840304</ci_population>
</cities>
<cities>
  <ci_name>Lyon</ci_name>
  <ci_population>1760456</ci_population>
</cities>

"EXPLICIT"

"EXPLICIT" - mode provides the most flexibility in generating the XML you want from a query result

example of a solution

select 
  1 as tag,
  null as parent,
  ci_name as [city!1!name],
  ci_population as [city!1!population]
from cities for xml explicit, root('cities')

result xml

<cities>
  <city name="Berlin" population="4262480" />
  <city name="Hamburg" population="1735568" />
  <city name="Paris" population="11840304" />
  <city name="Lyon" population="1760456" />
</cities>
[ Strona główna ]