Inserting XML Schema in to SQL Server Db.

Introduction

I am writing this just for a reference of an issue which i encountered, while inserting an nlog configuration in to SQL server DB.

Background

I was trying to insert an XML schema (NLog Configuration) in to a database field and I got an exception saying "XML parsing: line 1, character 290, undeclared prefix".

I googled a little bit and found the solution for that 

XML Schema

Below is the schema which i wanted to insert in to the DB.

<target  xsi:type="File" name="TestXmlConfigTarget" fileName="C:\temp\logs\log.csv" archiveFileName="C:\temp\logs\test_log.csv.log" archiveEvery="Month" keepFileOpen="false" encoding="UTF-8" maxArchiveFiles="30" archiveNumbering="Rolling" archiveAboveSize="1000000" concurrentWrites="true">
   <layout xsi:type="CsvLayout">
      <column name="DateStamp" layout="${date:format=MM-\dd-\yyyy HH\:mm\:ss K}" />
   </layout>
</target>

Problem SQL Script. 

DECLARE @txml xml= N'<target  xsi:type="File" name="TestXmlConfigTarget" fileName="C:\temp\logs\log.csv" archiveFileName="C:\temp\logs\test_log.csv.log" archiveEvery="Month" keepFileOpen="false" encoding="UTF-8" maxArchiveFiles="30" archiveNumbering="Rolling" archiveAboveSize="1000000" concurrentWrites="true">
      <layout xsi:type="CsvLayout">
        <column name="DateStamp" layout="${date:format=MM-\dd-\yyyy HH\:mm\:ss K}" />       
      </layout>
    </target>'

SELECT @TXML 

While trying to execute i got an exception like below.

Msg 9459, Level 16, State 1, Line 1
XML parsing: line 1, character 290, undeclared prefix.

Analyzed it a bit and I realized that the "xsi:" is the trouble maker.

Solution

To Solve this issue , i added a xmlns name space  in to the schema root.

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  in to the schema  and now the SQL looks like 

DECLARE @txml xml= N'<target  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="File" name="TestXmlConfigTarget" fileName="C:\temp\logs\log.csv" archiveFileName="C:\temp\logs\test_log.csv.log" archiveEvery="Month" keepFileOpen="false" encoding="UTF-8" maxArchiveFiles="30" archiveNumbering="Rolling" archiveAboveSize="1000000" concurrentWrites="true">
      <layout xsi:type="CsvLayout">
        <column name="DateStamp" layout="${date:format=MM-\dd-\yyyy HH\:mm\:ss K}" />       
      </layout>
    </target>'

SELECT @TXML 

And this solved my problem..

After fixing the schema issue ,i could able to insert the variable in to appropriate Db field with out any issue ..

Hope this may help some body ..