Original: use SQL statements to export tables in XML format

Sometimes we need to save the data read from the database as XML directly. Here we can achieve this through SQL statements.

First, create a table:

Code

 Use [IP]
Go
/***** Object: Table [DBO]. [sitedata] script Date: 17:41:11 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Set ansi_padding on
Go
Create Table [DBO]. [sitedata] (
[ID] [int] identity (1, 1) not null,
[Sitename] [varchar] (50) Collate chinese_prc_ci_as not null,
[Siteadd] [varchar] (100) Collate chinese_prc_ci_as not null,
[Baidusort] [int] not null constraint [df_sitedata_baidusort] default (0 )),
[Baidushoulu] [int] not null constraint [df_sitedata_baidushoulu] default (0 )),
Constraint [pk_sitedata] primary key clustered
(
[ID] ASC
) With (pad_index = OFF, ignore_dup_key = OFF) on [primary]
) On [primary]

Go
Set ansi_padding off

The table data is as follows:

 

The fields in the table are: Number, website name, website address, Baidu ranking, and Baidu.

Now we can export this table to XML format. The SQL statement is as follows:

Code

 Select
1 As tag,
Null as parent,
Sitename as [site! 1! Website name],
Siteadd as [site! 1! Website address],
Baidusort as [site! 1! Baidu ranking],
Baidushoulu as [site! 1! Baidu indexing]
From sitedata order by baidushoulu for XML explicit

After running this statement, we can see the following in the running result of SQL server2005:

We can see that the content of the XML node can be customized in SQL and distance. At this time, just click the output result, you can export a piece of code in XML format.

We will get:

 <Site name = "blog Park" website address = "http://www.cnblogs.com/" Baidu ranking = "20" Baidu indexed = "6020000"/>

The XML code we get now does not contain the xml header and the encoding format. If we want to be more foolish, we can add the following content directly in the background:

Code

 // Content is the string we get after reading the database
Content = content. replace ("& lt;", "<"). replace ("& quot ;","\""). replace ("& gt;", "> ");
// Customize the XML encoding format and parent node name
Content = "<? XML version = \ "1.0 \" encoding = \ "UTF-8 \"?> <Start> "+ content +" </start>
// Output XML to the local path using
(Streamwriter Sw = new streamwriter (@ "E: \ softupdate \ sql2xml. xml") {Sw. Write (content );}

In this way, we can export and save the data in the database to XML format through a trigger event on the page.