CODEWITHSUNDEEP
sql-serverxmldatabase
user3689706
user3689706

Reputation: 59

Inserting XML into SQL Server database and then querying it

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.

Upvotes: 0

Views: 659

Answers (2)

lheria
lheria

Reputation: 601

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

Upvotes: 1

Dour High Arch
Dour High Arch

Reputation: 21711

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.

Upvotes: 0

Related Questions