CODEWITHSUNDEEP
sql-serverxml
Sam
Sam

Reputation: 29009

Storing xml in SQL database

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?

Upvotes: 3

Views: 3395

Answers (2)

Paul Sasik
Paul Sasik

Reputation: 81469

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.

Upvotes: 1

Yuck
Yuck

Reputation: 50845

The problem is your gml prefix. You need to do something like:

<AX_Wald xmlns:gml="http://someUri/" gml:id="DESHPDHK0000u2xK">

Otherwise you don't have a valid XML document. You can verify that SQL Server will accept the document with this quick test:

DECLARE @X XML =
    N'<AX_Wald xmlns:gml="http://someUri/" gml:id="DESHPDHK0000u2xK" />';

SELECT @X;

Upvotes: 0

Related Questions