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 ..