Inserting XML into SQL Server database and then querying it

Question

I have a program that inserts XML data into an XML column in SQL Server, and I am finding that SQL Server is changing the formatting of the data of the source XML on insert. This is making it very difficult to query the data afterwards using the same same source XML.

For example, an extremely simple demo to show the issue:

INSERT INTO XMLRecord (XMLData) VALUES ('<Test></Test>')

Select * from XMLrecord

Returns:

<Test />

Now, if I try to run a query to see if my initial XML exists in the database:

select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test></Test>'

0 rows returned;

However, if the source XML doesn't contain "empty" tags, then the insert and searching works fine:

INSERT INTO XMLRecord (XMLData) VALUES ('<Test>Test Values</Test>')

Select * from XMLrecord

Returns:

<Test>Test Values</Test>

Now, if I try to run a query to see if my initial XML exists in the database:

select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test>Test Values</Test>'

1 rows returned;

I am hoping somebody can assist with a solution on how to get this to work. How can I take a source XML block (of unknown size, content etc), insert it into the database and then query the database for an EXACT match on the XML that I just inserted.

The data needs to be stored in the DB as XML format as i am also using it for numerous other xQueries.

Thanks very much.

If you want to match on the exact string, then you'll need to store the value as a string instead of XML. You can still cast it as XML for XQuery. — Dan Guzman
– Dan Guzman, Commented Apr 5, 2015 at 1:29

lheria · Accepted Answer · 2015-04-05 01:33:57Z

The Xml datatype only guarantees the content is preserved. From this MSDN reference:

Native storage as xml data type

The data is stored in an internal representation that preserves the XML content of the data. This internal representation includes information about the containment hierarchy, document order, and element and attribute values. Specifically, the InfoSet content of the XML data is preserved. For more information about InfoSet, visit http://www.w3.org/TR/xml-infoset. The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

As a result, if you absolutely need to preserve the shape and structure of the original Xml, you'll likely need to store it as nvarchar(max) and cast to xml when you need to perform XQueries. If the casting to xml is not too expensive for your use case, you could use a computed column to present the Xml casted version:

create table XMLRecord (
   XMLData nvarchar(max),
   XMLDataAsXml as cast(XMLData as xml)
)

INSERT INTO XMLRecord (XMLData) VALUES ('<Test></Test>')
INSERT INTO XMLRecord (XMLData) VALUES ('<Test>Test Values</Test>')

Select * from XMLrecord;
-- Returns records and shows Xml casted version is different for empty record
select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test></Test>';
-- returns 1
select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test>Test Values</Test>';
-- returns 1

select XmlDataAsXml.value('Test[1]', 'nvarchar(max)') from XmlRecord;
-- returns 2 rows, 1 with empty string and 1 with Test Values as expected

If the cast to xml overhead is too expensive for your needs, you may need to store it in both formats (2 columns).

Dour High Arch · Accepted Answer · 2015-04-05 01:25:41Z

0

XML and NVARCHAR are different data types; they have different behaviors and mixing them up is going to make your code very mixed up.

You cannot compare XML with predicates like =, > and such, you must use XQuery predicates like .value:

SELECT * FROM XmlRecord WHERE XmlData.value('/Test[1]', 'NVARCHAR(max)') = 'Test Values'

The XQuery expression /Test[1] will extract the contents of your first <Test> element, and .value(..., 'NVARCHAR(max)') will convert it to an NVARCHAR so you can use the = comparison.

answered Apr 5, 2015 at 1:25

Dour High Arch

21.8k31 gold badges78 silver badges93 bronze badges

Collectives™ on Stack Overflow

Inserting XML into SQL Server database and then querying it

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related