在SQL Server 2008 R2 express中存储XML文件

本文关键字：存储 XML 文件 express R2 SQL Server 2008 | 更新日期: 2023-09-27 18:06:43

我试图将XML数据存储到SQL Server 2008 R2 Express数据库中，每个XML文件都有不同的数据。最简单的方法是什么?

什么是最好的选择，请举例说明

在SQL Server 2008 R2 express中存储XML文件

我认为最简单的方法是创建一个存储过程来为您处理存储。然后，您可以通过优先的ORM检索它，并让c#为您反序列化它。

CREATE TABLE [dbo].[MyXmlStorage] 
( 
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [FileName] [nvarchar](255) NOT NULL, 
    [Xml] [xml] NOT NULL, 
    CONSTRAINT [PK_MyXmlStorage] 
    PRIMARY KEY CLUSTERED  ([Id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
CREATE PROCEDURE [dbo].[InsertXml]
    (@filePathFull nvarchar(255)) 
AS 
   DECLARE @xmlAsString VARCHAR(MAX) 
   DECLARE @sql nvarchar(max) 
   DECLARE @xml XML 
   DECLARE @Rms_FileId nvarchar(50) 
   DECLARE @Rms_Id nvarchar(50) 
   DECLARE @Rms_Type nvarchar(50) 
   DECLARE @Rms_Timestamp nvarchar(50) 
BEGIN 
    SET @sql = 'SELECT @xmlAsString = x.y FROM OPENROWSET( BULK ''' + RTRIM(@filePathFull) + ''', SINGLE_CLOB) x(y)' 
    exec sp_executesql @sql,N'@xmlAsString VARCHAR(MAX) OUTPUT',@xmlAsString OUTPUT 
    set @xml = CONVERT(XML,@xmlAsString)  
    INSERT INTO MyXmlStorage([FileName],[Xml]) 
    VALUES (@filePathFull, @xml) 
END

然后像这样运行:

exec InsertXml N'C:'files'xmlfile.xml'

好，这是一个将xml的值存储到表中的示例。我没有尝试过这个代码，但它应该是工作的，但至少它应该澄清如何做预期的

   /* Imagine your xml looks something like this
    <Content>
        <Title>Text</Title>
        <Value>15</Value>
    </Content>
    */
    CREATE TABLE [dbo].[MyXmlStorage] 
    ( 
        [Id] [int] IDENTITY(1,1) NOT NULL, 
        [Title] [nvarchar](100) NOT NULL, 
        [Value] int NOT NULL,
        CONSTRAINT [PK_MyXmlStorage] 
        PRIMARY KEY CLUSTERED  ([Id] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
    CREATE PROCEDURE [dbo].[InsertXml]
        (@filePathFull nvarchar(255)) 
    AS 
       DECLARE @xmlAsString VARCHAR(MAX) 
       DECLARE @sql nvarchar(max) 
       DECLARE @xml XML 
       DECLARE @Rms_FileId nvarchar(50) 
       DECLARE @Rms_Id nvarchar(50) 
       DECLARE @Rms_Type nvarchar(50) 
       DECLARE @Rms_Timestamp nvarchar(50) 
    BEGIN 
        SET @sql = 'SELECT @xmlAsString = x.y FROM OPENROWSET( BULK ''' + RTRIM(@filePathFull) + ''', SINGLE_CLOB) x(y)' 
        exec sp_executesql @sql,N'@xmlAsString VARCHAR(MAX) OUTPUT',@xmlAsString OUTPUT 
        set @xml = CONVERT(XML,@xmlAsString)  
        /* Use xpath to query nodes for values inside the Content tag*/
        INSERT INTO MyXmlStorage([Title],[Value]) 
        SELECT 
            x.y.value('title[1]/text()[1]', 'nvarchar(100)') AS title,
            x.y.value('value[1]/text()[1]', 'int') AS value
        FROM @xml.nodes('//Content') AS x(y)
    END 
)
相关文章：