将 SQL Server 数据库导出到 XML 优化 [英] Export SQL Server database to XML Optimization

问题描述

我有用于文章的 SQL Server 数据库.

文章表:

创建表 [dbo].[T_Articale]([id] [int] IDENTITY(1,1) 非空，[标题] [nvarchar](100) NULL，[文本] [nvarchar](max) NULL,[LocationID] [int] NULL，[TribeID] [int] NULL，[ArticaleText] [nvarchar](max) NULL,[ArticaleDate] [date] NULL,[SearchID] [int] NULL，[ClassificationID] [int] NULL，[CountryID] [int] NULL,[isLocal] [int] NULL，[注意] [nvarchar](150) NULL,约束 [PK_T_Articale]主键集群)

其中 ArticaleText 是带有格式(HTML 标签)的文章内容

Files 表:

创建表 [dbo].[T_Articale_Files]([id] [int] IDENTITY(1,1) 非空，[Article_id] [int] NULL，[文件名] [nvarchar](50) NULL，[FileData] [varbinary](max) NULL,[FileData2] [nvarchar](max) NULL,约束 [PK_T_Articale_Files]主键集群)

这里用户可以附上带有文章的文件FileData 二进制列是实际文件

我有一个将数据库导出为 XML 的功能

var xmlFileData = "";数据集 ds = 新数据集();var tables = new[] {//数据库表列表 };foreach(表中的var表){var query = "SELECT * FROM " + table;SqlConnection conn = GetConnection();SqlCommand cmd = new SqlCommand(query, conn);conn.Open();SqlDataAdapter da = 新的 SqlDataAdapter(cmd);数据表 dt = 新数据表(表)；da.Fill(dt);conn.Close();conn.Dispose();ds.Tables.Add(dt);}//xmlFileData = ds.GetXml();var xmlstream = new StringWriter();ds.WriteXml(xmlstream, XmlWriteMode.WriteSchema);字符串 xmlWithSchema = xmlstream.ToString();File.WriteAllText("D://SelectiveDatabaseBackup.xml", xmlWithSchema);

我的问题是现在数据库增长到 700MB(如果我使用带有数据的脚本)我的 XML 函数给出内存不足异常

哪些修复或优化可以解决该问题(聚类、索引...)(数据库会继续增长)

编辑

为什么我需要 XML??

情况是我有一些计算机无法访问服务器并且没有安装 SQL Server，所以我需要在没有 DB Engine 的情况下使这些数据可用.所以第一个想法是 XML.它不是用于备份目的.

这是一个 Windows 窗体应用程序.

解决方案

让我们考虑一个生成 XML 的示例:

CREATE PROC GenerateXMLproc作为选择 [名称]、产品编号、颜色从 dbo.ProductFOR XML 原始(‘产品’)，元素，根(‘产品’)

现在我们将讨论上述用于保存此 XML 数据的选项:

1. 使用 CLR 存储过程:在这种方法中，我们可以创建一个 CLR 存储过程，它将获取生成的 XML 数据、文件名和文件的位置作为输入参数.然后它可以根据要求处理该 XML，并将该 XML 文件保存到所需的位置.

创建 CLR 存储过程:

[Microsoft.SqlServer.Server.SqlProcedure]public static void SaveXMLOutput(SqlXml XmlData, SqlString Filename){//将传递给SP的XML数据保存到文件位置//指定提供给SP的文件名XmlDocument xmlDoc = new XmlDocument();SqlPipe 输出 = SqlContext.Pipe;xmlDoc.LoadXml(XmlData.Value);xmlDoc.Save(文件名.值);}

<块引用>

//这将给出一个 DLL ‘SaveXMLOutput.dll’在 SQL Server 中创建程序集

CREATE ASSEMBLY SaveXMLOutputAssembly来自C:\Temp\SaveXMLOutput.dll"WITH PERMISSION_SET = EXTERNAL_ACCESS

<块引用>

--数据库的‘trustworthy’属性必须先设置为‘ON’对 aseembly 使用 EXTERNAL_ACCESS.

–这可以使用 ALTER DATABASE [DataBaseName] SET trustworthy 来完成开启

从导入的 DLL/程序集创建存储过程:

创建程序 SaveXMLOutput@xmldata XML，@filename nvarchar(1024)作为外部名称 SaveXMLOutputAssembly.[XMLOutput].SaveXMLOutput

<块引用>

--XMLOutput 是类名，SaveXMLOutput 是类名类中定义的SP

——执行这个 SP

execute SaveXMLOutput ‘传递其他 SP 生成的 XML 数据’,' C:\Temp\MyXML.xml'

I have SQL Server database for articles.

Articale table:

CREATE TABLE [dbo].[T_Articale]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NULL,
    [Text] [nvarchar](max) NULL,
    [LocationID] [int] NULL,
    [TribeID] [int] NULL,
    [ArticaleText] [nvarchar](max) NULL,
    [ArticaleDate] [date] NULL,
    [SearchID] [int] NULL,
    [ClassificationID] [int] NULL,
    [CountryID] [int] NULL,
    [isLocal] [int] NULL,
    [Note] [nvarchar](150) NULL,

    CONSTRAINT [PK_T_Articale] 
        PRIMARY KEY CLUSTERED 
)

Where ArticaleText is the content of the article with formatting (HTML tags)

Files table:

CREATE TABLE [dbo].[T_Articale_Files]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Artricle_id] [int] NULL,
    [FileName] [nvarchar](50) NULL,
    [FileData] [varbinary](max) NULL,
    [FileData2] [nvarchar](max) NULL,

    CONSTRAINT [PK_T_Articale_Files] 
        PRIMARY KEY CLUSTERED 
)

Here the user can attach files with the article FileData binary column is the actual file

I have a function to export the database to XML

var xmlFileData = "";
DataSet ds = new DataSet();
var tables = new[] { //List of DB Tables  };

foreach (var table in tables)
{
    var query = "SELECT * FROM " + table;

    SqlConnection conn = GetConnection();
    SqlCommand cmd = new SqlCommand(query, conn);

    conn.Open();

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable(table);
    da.Fill(dt);

    conn.Close();
    conn.Dispose();

    ds.Tables.Add(dt);
}

//  xmlFileData = ds.GetXml();
var xmlstream = new StringWriter();
ds.WriteXml(xmlstream, XmlWriteMode.WriteSchema);
string xmlWithSchema = xmlstream.ToString();
File.WriteAllText("D://SelectiveDatabaseBackup.xml", xmlWithSchema);

My problem is now the database grows to 700MB (if I take a script with data) my XML function give out of memory exception

What fixes or optimizations can solve that problem (clustering, indexing ...) (the database will continue to grow)

Edit

Why I need XML??

The situation is I have some computers that can not access the server and does not have SQL Server installed so I need to make that data available without DB Engine. So the first thought was XML. It is not for backup purpose.

It is a Windows Forms application.

解决方案

Let’s consider an example for generating a XML:

CREATE PROC GenerateXMLproc

AS

SELECT [Name], ProductNumber, Color

FROM dbo.Product

FOR XML raw(‘Product’), elements, root(‘Products’)

Now we’ll discuss the above mentioned options for saving this XML data:

1. Using CLR Stored Procedure: In this method we can create a CLR stored procedure which will take the XML data generated, file name & location of the file as input parameters. And then it can process that XML as per the requirements and can save that XML file to the desired location.

Creating CLR stored procedure:

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SaveXMLOutput(SqlXml XmlData, SqlString Filename)

{

             //Save the XML data being passed to the SP to a file location

      //specify the name of the file suppiled to the SP

      XmlDocument xmlDoc = new XmlDocument();

      SqlPipe output = SqlContext.Pipe;

      xmlDoc.LoadXml(XmlData.Value);

      xmlDoc.Save(Filename.Value);

}

//This will give a DLL ‘SaveXMLOutput.dll’ Creating Assembly in SQL Server

CREATE ASSEMBLY SaveXMLOutputAssembly

from ‘C:\Temp\SaveXMLOutput.dll’

WITH PERMISSION_SET = EXTERNAL_ACCESS

–‘ trustworthy ’ property of the database must be set to ‘ON’ before using the EXTERNAL_ACCESS for the aseembly.

–This can be done using ALTER DATABASE [DataBaseName] SET trustworthy ON

Creating stored procedure from the Imported DLL/Assembly:

CREATE PROCEDURE SaveXMLOutput

@xmldata XML,

@filename nvarchar(1024)

AS

EXTERNAL NAME SaveXMLOutputAssembly.[XMLOutput].SaveXMLOutput

–XMLOutput is the name of class and SaveXMLOutput is the name of the SP as defined in the class

–Executing this SP

execute SaveXMLOutput ‘Pass the XML Data generated from other SP’

,‘ C:\Temp\MyXML.xml’

这篇关于将 SQL Server 数据库导出到 XML 优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！