Storing xml in SQL database

Question

I have to store XML content in a SQL Server 2008R2 database. The XML is not a full fledged file but contains only simple elements.

I created a table with a column of type [xml], but when I try to store an item inside I get an error.

For example, if I try to store this (simplified) item:

<AX_Wald gml:id="DESHPDHK0000u2xK">
  <gml:identifier codeSpace="http://www.adv-online.de/">urn:adv:oid:DESHPDHK0000u2xK</gml:identifier>
  <vegetationsmerkmal>1300</vegetationsmerkmal>
</AX_Wald>

I get the error (translated):

Msg 9459 - XML parsing: line 1, character 35, undeclared prefix.

I guess this is the namespace missing, but I don't want to add all possible namespaces to every single item in my database. Is there a way to resolve this without changing the XML? I need to be able to access and compare the original XML later on, so I'd prefer to leave it unchanged, if possible.

So is there a way to tell SQL Server to either tell SQL Server to ignore namespaces, or to add namespaces for the column which contains these items once for the whole table?

Answer 1

Unless you'll be using/needing the XML functionality in SQL Server (e.g. performing XML queries) you could simply think of these XML snippets as structured text and just store them as nvarchar instead of an XML type.

You can also designate your XML type as a fragment which should be more forgiving and flexible within your scheme:

An XML fragment is an XML instance that is missing a single top-level element.