Export tables directly to XML format via SQL statements _mssql

First set up a table:

Copy Code code as follows:

Use [IP]
Go
/****** object: Table [dbo]. [Sitedata] Script Date: 09/11/2010 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] (m) COLLATE chinese_prc_ci_as not NULL,
[Siteadd] [varchar] (MB) 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 data in the table are as follows:

The fields in the table are: number, website name, website address, Baidu rankings, Baidu included.

Now we're going to export this table directly to the XML format, and the SQL statement is as follows:

Copy Code code 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 rankings],
Baidushoulu as [site!1! Baidu included]
From Sitedata to Baidushoulu for XML EXPLICIT

After running this statement, we can see the following in the results of the SQL Server2005 run:

We can see that the contents of this XML node can be customized in SQL and distance, and we can export a piece of code in XML format as soon as we click on the output.

We are going to get:

Copy Code code as follows:

<site website name = "Blog Park" website address = "http://www.jb51.net/" Baidu rank = "20" Baidu is included = "6020000"/>

The XML code we're getting now is a header and code format that doesn't contain XML, and if we want to do something more goofy, some of the following can be added directly to the background customization:

Copy Code code as follows:

Content is the string that we get when we read the database
Content = content. Replace ("<", "<"). Replace ("" "," \ "). Replace (">", ">");
Custom XML encoding format and parent node name
Content = "<?xml version=\" 1.0\ "encoding=\" utf-8\ "?><start>" + content + "</start>
Output XML to local path using
(StreamWriter sw = new StreamWriter (@ "E:\SoftUpdate\sql2xml.xml")) {SW. Write (content); }