28

I've got a simple table and want to store its content into a xml on the harddrive. There should be one root element for the whole table, one element per table row and one child element per table column.

What possibilities do I have?

Thanks a lot Tomas

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tomas Walek
  • 2,516
  • 2
  • 23
  • 37

3 Answers3

52

And if you need more control over how the resulting XML looks like, check out the new FOR XML PATH statement in SQL Server 2005 and newer.

A statement like this (based on the infamous Northwind database):

SELECT 
   CustomerID as "@CustomerID",
   CompanyName,
   Address as "address/street",
   City as "address/city",
   Region as "address/region",
   PostalCode as "address/zip",
   Country as "address/country",
   ContactName as "contact/name",
   ContactTitle as "contact/title",
   Phone as "contact/phone", 
   Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer')

will result in an output like this:

  <Customer CustomerID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <address>
      <street>Obere Str. 57</street>
      <city>Berlin</city>
      <zip>12209</zip>
      <country>Germany</country>
    </address>
    <contact>
      <name>Maria Anders</name>
      <title>Sales Representative</title>
      <phone>030-0074321</phone>
      <fax>030-0076545</fax>
    </contact>
  </Customer>

That's rather tricky to get any other way....

Marc

scw
  • 5,450
  • 8
  • 36
  • 49
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • How do I do the reverse i.e. convert XML back to a table – Raihan Iqbal Dec 14 '17 at 04:57
  • 1
    @RaihanIqbal: look at the built-in XQuery support in T-SQL - using `.query()`, `.nodes()` and `.value()`, you can definitely do that - but that's a whole different question :-) – marc_s Dec 14 '17 at 05:37
  • 2
    This is good stuff! I'd only add that the Questioner wanted "one root element for the whole table". To do this, simply add `ROOT('Root')` like so: `FOR XML PATH('Customer'), ROOT('Customers')` – MikeTeeVee Jun 30 '18 at 21:37
45

Use the FOR XML in your query.

E.g: select * from table1 FOR XML AUTO

see this --> http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1265579,00.html

Alternatively, you can create your own XML in your t-sql code through cursors or in your application code, the longer way of doing it.

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
Bhaskar
  • 10,537
  • 6
  • 53
  • 64
0

SELECT CAST('1' AS XML)

This Query fire in sql and your copy data put inside then show XML Result.

Fezal halai
  • 756
  • 7
  • 14