Export the table to XML format directly using SQL statements

First, create a table:

Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
<Site name = "blog Park" website address = "http://www.jb51.net/" 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:
Copy codeThe Code is as follows:
// Content is the string we get after reading the database
Content = content. Replace ("<", "<"). Replace ("," \ ""). Replace (">", "> ");
// 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 );}