Storing XML file in SQL Server 2008 R2 express

Question

I am trying to store XML data into a SQL Server 2008 R2 Express database, every xml file has different data. What is the easiest way to do this?

What are the best options please explain with example.

Use a `XML` type column, store the XML inside that column .... — marc_s, Jun 03 '14 at 15:08
See e.g. http://stackoverflow.com/questions/18090977/designing-table-to-store-xml-data-in-sql-server-2008-r2 — marc_s, Jun 03 '14 at 15:18

score 2 · Answer 1 · edited Jun 03 '14 at 15:58

I believe easiest way will be to create a stored procedure to handle the storage for you. You can then retrieve it by an ORM of preferage and let C# deserialize it for you.

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

Then run it like this:

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

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! That way, you don't need any of the messy `
` tags either! — marc_s, Jun 03 '14 at 15:58
Thanks for clearing that out, got a bit frustrated there for a while :) — JFM, Jun 03 '14 at 16:07
@JFM Thanks alot it worked, but i want to store each xml element value rather than whole xml file as a string. — user3202862, Jun 04 '14 at 11:04

score 1 · Accepted Answer · answered Jun 04 '14 at 19:42

Ok, this is an example for storing the values of the xml into the table instead. I havent't tried this code but it should be working but at least it should clarify how to do as expected.

   /* 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 
)

Storing XML file in SQL Server 2008 R2 express

2 Answers2