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?

Paul Sasik · Accepted Answer · 2013-03-05 22:25:06Z

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.

edited Mar 5, 2013 at 22:25

answered Oct 27, 2011 at 14:28

Paul Sasik

81.9k23 gold badges152 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yuck Over a year ago

Even a fragment will require namespaces to be defined. A fragment just allows you to have multiple "root" elements. They still need to be well-formed.

Sam Over a year ago

I'll just use nvarchar(max) instead of xml (the text type being deprecated). Thanks for the hint!

Yuck · Accepted Answer · 2011-10-27 14:28:03Z

0

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;

answered Oct 27, 2011 at 14:28

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Collectives™ on Stack Overflow

Storing xml in SQL database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related