Insert Xml into SQL Server 2008 Database

INSERT XML into SQL Server 2008 database

Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line.

I typically get around using the CONVERT function which allows me to instruct SQL Server to skip those instructions - use something like this:

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>......', 2));

It has definitely helped me get various encoded XML stuff into SQL Server.

See the MSDN docs on CAST and CONVERT - a bit down the page there's a number of styles you can use for CONVERT with XML and some explanations about them.

Inserting XML documents into SQL Server 2008 database

Are you using a stored procedure? You can specify the file name as a parameter.

Something like...

CREATE PROCEDURE sp_XMLLoad
   @FileName
AS SET NOCOUNT ON
SELECT  X.article.query('id').value('.', 'INT'),
        X.article.query('article').value('.', 'VARCHAR(50)'),   
        X.article.query('/doc/text()').value('.', 'VARCHAR(MAX)')
FROM (
      SELECT CAST(x AS XML)
      FROM OPENROWSET(
            BULK @FileName,
            SINGLE_BLOB) AS T(x)

Not exactly like that ... you'll need to add quotes around the @Filename I bet. Maybe assemble it with quotes and then use that variable.

If you're using SSIS, you can then pump all the files from a directory to the stored procedure, or to the SSIS code used.

How to insert xml data from URL into SQL Server 2008 database by call stored procedure in C#

Step 1: create your stored procedure:

CREATE PROCEDURE dbo.StoreXmlData (@XmlData XML)
AS BEGIN
    INSERT INTO dbo.YourXmlTable(XmlColumnName)
    VALUES(@XmlData)
END

This assumes you're on SQL Server 2005 or newer, and you're storing your XML in a column that is really of datatype XML. Adapt table and column name as needed to match your situation.

Step 2: from C#, call your stored procedure using standard ADO.NET classes:

string sqlStmt = "dbo.StoreXmlData";  // name of your stored procedure

// here, you need to use *YOUR* connection string instead of my demo one...    
using(SqlConnection conn = new SqlConnection("server=.;database=test;integrated security=SSPI;"))
using (SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    // make sure to tell ADO.NET you're about to call a stored procedure!
    cmd.CommandType = CommandType.StoredProcedure;

    // define the parameter and set its value
    cmd.Parameters.Add("@YourData", SqlDbType.Xml);
    cmd.Parameters["@YourData"].Value = doc.DocumentElement.InnerXml;

    // standard ADO.NET - open connection, execute query, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

That's really all there is!

Update: OK, so you want to actually shred the XML into individual columns - in that case, you need to do something like this in your stored procedure:

CREATE PROCEDURE dbo.StoreXmlData (@XmlData XML)
AS 
BEGIN
    INSERT INTO dbo.YourDataTable(RowNo, AccountID, OwnerID, AccountOwner)
        SELECT
            RowNo = Acc.value('@no', 'int'),
            AccountID = Acc.value('(FL[@val="ACCOUNTID"])[1]', 'nvarchar(50)'),
            OwnerID = Acc.value('(FL[@val="SMOWNERID"])[1]', 'nvarchar(50)'),
            AccountOwner = Acc.value('(FL[@val="Account Owner"])[1]', 'nvarchar(50)')
            -- and so on to grab all the items you need
        FROM
            @XmlData.nodes('/response/result/Accounts/row') AS Resp(Acc)
END

Insert xml to database with full declaration

How can I force sql server to insert above xml string with full
declaration ?

It is not possible. Limitations of the xml Data Type

The XML declaration PI in an instance is not preserved when the
instance is stored in the database.

Storing XML file in SQL Server 2008 R2 express

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 
)

SQL Server 2008R2 and creating XML document

You missed a step in your testing. Try this:

SELECT CONVERT(XML, '<?xml version="1.0" encoding="utf-8"?>')

It will return an empty cell.

Based on what you are doing (i.e. converting to VARCHAR in the end), there is no reason to start with the XML datatype. You might as well remove the , TYPE from the FOR XML clause and then just concatenate @sXMLVersion + @xTempXML

The reason this is happening is noted here: Limitations of the xml Data Type

The XML declaration PI, for example, <?xml version='1.0'?>, is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration (<?xml ... ?>) and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2. All other PIs in the XML instance are preserved.

How to properly handle extracting data from an XML field / variable is noted here: XML Best Practices (under "Text Encoding")

SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from the server comes out in UTF-16 encoding. If you want a different encoding, you have to perform the required conversion on the retrieved data. Sometimes, the XML data may be in a different encoding. If it is, you have to use care during data loading. For example:

• If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to an XML column, variable, or parameter without any problems.
• If the encoding is not Unicode and is implicit, because of the source code page, the string code page in the database should be the same as or compatible with the code points that you want to load. If required, use COLLATE. If no such server code page exists, you have to add an explicit XML declaration with the correct encoding.
• To use an explicit encoding, use either the varbinary() type, which has no interaction with code pages, or use a string type of the appropriate code page. Then, assign the data to an XML column, variable, or parameter.

Example: Explicitly Specifying an Encoding
Assume that you have an XML document, vcdoc, stored as varchar(max) that does not have an explicit XML declaration. The following statement adds an XML declaration with the encoding "iso8859-1", concatenates the XML document, casts the result to varbinary(max) so that the byte representation is preserved, and then finally casts it to XML. This enables the XML processor to parse the data according to the specified encoding "iso8859-1" and generate the corresponding UTF-16 representation for string values.

SELECT CAST(
    CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX))
  AS XML)

---

The following S.O. questions are related:

• SQL Server 2008 - Add XML Declaration to XML Output

• How to add xml encoding <?xml version="1.0" encoding="UTF-8"?> to xml Output in SQL Server

Bulk insert into SQL Server 2008 R2 from XML RAW data

As you are dealing with fairly big XML documents, I recommend at this point to use a .net shredder. You can do that in a CLR procedure or an external tool. You could also use the build in xquery of SQL Server, but that will be slow.

However, looking at this and your previous question (Dump data into single XML file from MS SQL Server 2008 R2), I am thinking you might be better of using something like the BCP utility or even replication. What are your exact requirements?

---

Related Topics